WIXLIB

Build Your Own Database Driven WebsiteUsing PHP and MySQL, 3rd Edition(First 4 Chapters)Thank you for downloading the first four chapters of KevinYank’s Build Your Own Database Driven Website Using PHP andMySQL, 3rd Edition.This excerpt encapsulates the Summary of Contents, Informationabout the Author and SitePoint, Table of Contents, Introduction,and the first four chapters of the book.We hope you find this information useful in evaluating the book.For more information, visit sitepoint.com

Summary of Contents of this ExcerptPreface .ix1. Installation . 12. Getting Started with MySQL . 293. Getting Started with PHP . 434. Publishing MySQL Data on the Web . 67Index. 345Summary of Additional Book Contents5. Relational Database Design. 856. A Content Management System . 1017. Content Formatting and Submission . 1438. MySQL Administration . 1659. Advanced SQL Queries. 18310. Binary Data. 19911. Cookies and Sessions in PHP. 22112. Structured PHP Programming. 235A. MySQL Syntax . 277B. MySQL Functions . 301C. MySQL Column Types . 321D. PHP Functions for Working with MySQL . 331

Build Your Own DatabaseDriven Website Using PHP &MySQLby Kevin Yank

Build Your Own Database Driven Website Using PHP &MySQLby Kevin YankCopyright 2004 SitePoint Pty. Ltd.Editor: Georgina LaidlawManaging Editor: Simon MackiePrinting History:First Edition: August 2001Second Edition: February 2003Third Edition: October 2004Index Editor: Bill JohncocksCover Design: Julian CarrollNotice of RightsAll rights reserved. No part of this book may be reproduced, stored in a retrieval system or transmittedin any form or by any means, without the prior written permission of the publisher, except in thecase of brief quotations embodied in critical articles or reviews.Notice of LiabilityThe author and publisher have made every effort to ensure the accuracy of the information herein.However, the information contained in this book is sold without warranty, either express or implied.Neither the authors and SitePoint Pty. Ltd., nor its dealers or distributors will be held liable for anydamages to be caused either directly or indirectly by the instructions contained in this book, or bythe software or hardware products described herein.Trademark NoticeRather than indicating every occurrence of a trademarked name as such, this book uses the namesonly in an editorial fashion and to the benefit of the trademark owner with no intention of infringement of the trademark.Published by SitePoint Pty. Ltd.424 Smith Street CollingwoodVIC Australia 3066.Web: www.sitepoint.comEmail: business@sitepoint.comISBN 0–9752402–1–8Printed and bound in the United States of America

About the AuthorAs Technical Director for SitePoint, Kevin Yank oversees all of its technical publications—books, articles, newsletters and blogs. He has written over 50 articles for SitePointon technologies including PHP, XML, ASP.NET, Java, JavaScript and CSS. He writes TheSitePoint Tech Times, SitePoint’s biweekly technical newsletter for Web developers, whichhas over 75,000 readers worldwide.When he’s not discovering new technologies, editing books, or catching up on sleep,Kevin can be found helping other up-and-coming Web developers in the SitePoint Forums.Kevin lives in Melbourne, Australia, with several potted plants. In his spare time he enjoysflying light aircraft and learning the fine art of improvised acting. Go you big red fire engine!About SitePointSitePoint specializes in publishing fun, practical, and easy-to-understand content for Webprofessionals. Visit http://www.sitepoint.com/ to access our books, newsletters, articlesand community forums.

To my parents, Cheryl andRichard, for making all thispossible.

ii

Table of ContentsPreface . ixWho Should Read This Book . xWhat’s In This Book . xThe Book’s Website . xiiiThe Code Archive . xiiiUpdates and Errata . xiiiThe SitePoint Forums . xivThe SitePoint Newsletters . xivYour Feedback . xiv1. Installation . 1Windows Installation . 2Installing MySQL . 2Installing PHP . 6Linux Installation . 12Removing Packaged Software . 13Installing MySQL . 14Installing PHP . 17Mac OS X Installation . 20Installing MySQL . 20Installing PHP . 22Mac OS X and Linux . 22Post-Installation Setup Tasks . 22If Your Web Host Provides PHP and MySQL . 25Your First PHP Script . 26Summary . 282. Getting Started with MySQL .An Introduction to Databases .Logging On to MySQL .So, What’s SQL? .Creating a Database .Creating a Table .Inserting Data into a Table .Viewing Stored Data .Modifying Stored Data .Deleting Stored Data .Summary .2929313434353738404141

Build Your Own Database Driven Website Using PHP & MySQL3. Getting Started with PHP . 43Introducing PHP . 43Basic Syntax and Commands . 45Variables and Operators . 47Arrays . 48User Interaction and Forms . 50Control Structures . 56Multipurpose Pages . 61Summary . 664. Publishing MySQL Data on the Web . 67A Look Back at First Principles . 67Connecting to MySQL with PHP . 69Sending SQL Queries with PHP . 71Handling SELECT Result Sets . 72Inserting Data into the Database . 75A Challenge . 80Summary . 80“Homework” Solution . 805. Relational Database Design . 85Giving Credit where Credit is Due . 85Rule of Thumb: Keep Things Separate . 87Dealing with Multiple Tables . 90Simple Relationships . 94Many-to-Many Relationships . 96Summary . 996. A Content Management System . 101The Front Page . 102Managing Authors . 105Deleting Authors . 107Adding Authors . 110Editing Authors . 112Magic Quotes . 115Managing Categories . 117Managing Jokes . 123Searching for Jokes . 123Adding Jokes . 129Editing and Deleting Jokes . 137Summary . 142ivOrder this 350 page hard-copy PHP/MySQL book now!

7. Content Formatting and Submission . 143Out with the Old . 144Regular Expressions . 145String Replacement with Regular Expressions . 148Boldface and Italic Text . 149Paragraphs . 149Hyperlinks . 150Matching Tags . 152Splitting Text into Pages . 155Putting it all Together . 157Automatic Content Submission . 162Summary . 1638. MySQL Administration . 165Backing up MySQL Databases . 166Database Backups using mysqldump . 167Incremental Backups using Update Logs . 168MySQL Access Control . 170Using GRANT . 171Using REVOKE . 174Access Control Tips . 174Locked Out? . 177Checking and Repairing MySQL Data Files . 178Summary . 1819. Advanced SQL Queries . 183Sorting SELECT Query Results . 183Setting LIMITs . 186LOCKing TABLES . 187Column and Table Name Aliases . 189GROUPing SELECT Results . 192LEFT JOINs . 194Limiting Results with HAVING . 197Summary . 19810. Binary Data . 199Semi-Dynamic Pages . 199Handling File Uploads . 204Assigning Unique File Names . 206Recording Uploaded Files in the Database . 208Binary Column Types . 209Storing Files . 210Viewing Stored Files . 212Order this 350 page hard-copy PHP/MySQL book now!v

Build Your Own Database Driven Website Using PHP & MySQLThe Complete Script .Large File Considerations .MySQL Packet Size .PHP Script Timeout .Summary .21522022022022011. Cookies and Sessions in PHP . 221Cookies . 221PHP Sessions . 225A Simple Shopping Cart . 228Summary . 23412. Structured PHP Programming . 235What is Structured Code? . 235The Need for Structured Code . 236Include Files . 238Types of Includes . 242Including HTML Content . 244Locating Include Files . 246Returning from Includes . 249Custom Functions and Function Libraries . 253Variable Scope and Global Access . 257Optional and Unlimited Arguments . 261Constants . 263Structure In Practice: Access Control . 265Summary . 274A. MySQL Syntax . 277ALTER TABLE . 277ANALYZE TABLE . 280CREATE DATABASE . 280CREATE INDEX . 281CREATE TABLE . 281DELETE . 283DESCRIBE . 284DROP DATABASE . 285DROP INDEX . 285DROP TABLE . 285EXPLAIN . 285GRANT . 286INSERT . 286LOAD DATA INFILE . 287LOCK/UNLOCK TABLES . 288viOrder this 350 page hard-copy PHP/MySQL book now!

OPTIMIZE TABLE .RENAME TABLE .REPLACE .REVOKE .SELECT .Joins .Unions .SET .SHOW .UNLOCK TABLES .UPDATE .USE .289289290290291295297297298299299300B. MySQL Functions . 301Control Flow Functions . 301Mathematical Functions . 301String Functions . 305Date and Time Functions . 309Miscellaneous Functions . 315Functions for Use with GROUP BY Clauses . 318C. MySQL Column Types . 321Numerical Types . 322Character Types . 324Date/Time Types . 327D. PHP Functions for Working with MySQL . 331mysql affected rows . 331mysql client encoding . 331mysql close . 332mysql connect . 332mysql create db . 333mysql data seek . 333mysql db name . 333mysql db query . 333mysql drop db . 334mysql errno . 334mysql error . 334mysql escape string . 334mysql fetch array . 335mysql fetch assoc . 335mysql fetch field . 335mysql fetch lengths . 336Order this 350 page hard-copy PHP/MySQL book now!vii

Build Your Own Database Driven Website Using PHP & MySQLmysql fetch object .mysql fetch row .mysql field flags .mysql field len .mysql field name .mysql field seek .mysql field table .mysql field type .mysql free result .mysql get client info .mysql get host info .mysql get proto info .mysql get server info .mysql info .mysql insert id .mysql list dbs .mysql list fields .mysql list processes .mysql list tables .mysql num fields .mysql num rows .mysql pconnect .mysql ping .mysql query .mysql real escape string .mysql result .mysql select db .mysql stat .mysql tablename .mysql thread id .mysql unbuffered query .Index 5Order this 350 page hard-copy PHP/MySQL book now!

Preface“Content is king.” Cliché, yes; but it has never been more true. Once you’vemastered HTML and learned a few neat tricks in JavaScript and Dynamic HTML,you can probably design a pretty impressive-looking Website. But your next taskmust be to fill that fancy page layout with some real information. Any site thatsuccessfully attracts repeat visitors has to have fresh and constantly updatedcontent. In the world of traditional site building, that means HTML files—andlots of ’em.The problem is that, more often than not, the people who provide the contentfor a site are not the same people who handle its design. Frequently, the contentprovider doesn’t even know HTML. How, then, is the content to get from theprovider onto the Website? Not every company can afford to staff a full-timeWebmaster, and most Webmasters have better things to do than copying Wordfiles into HTML templates, anyway.Maintenance of a content-driven site can be a real pain, too. Many sites (perhapsyours?) feel locked into a dry, outdated design because rewriting those hundredsof HTML files to reflect a new look would take forever. Server-side includes(SSIs) can help alleviate the burden a little, but you still end up with hundredsof files that need to be maintained should you wish to make a fundamental changeto your site.The solution to these headaches is database-driven site design. By achievingcomplete separation between your site’s design and the content you want topresent, you can work with each without disturbing the other. Instead of writingan HTML file for every page of your site, you need only to write a page for eachkind of information you want to be able to present. Instead of endlessly pastingnew content into your tired page layouts, create a simple content managementsystem that allows the writers to post new content themselves without a lick ofHTML!In this book, I’ll provide you with a hands-on look at what’s involved in buildinga database-driven Website. We’ll use two tools for this, both of which may benew to you: the PHP scripting language and the MySQL relational databasemanagement system. If your Web host provides PHP and MySQL support, you’rein great shape. If not, we’ll be looking at the setup procedures under Linux,Windows, and Mac OS X, so don’t sweat it.