WIXLIB

PHP & MySQL FORDUMmIES‰2NDEDITIONby Janet Valade

PHP & MySQL FORDUMmIES‰2NDEDITION

PHP & MySQL FORDUMmIES‰2NDEDITIONby Janet Valade

PHP & MySQL For Dummies , 2nd EditionPublished byWiley Publishing, Inc.111 River StreetHoboken, NJ 07030-5774Copyright 2004 by Wiley Publishing, Inc., Indianapolis, IndianaPublished by Wiley Publishing, Inc., Indianapolis, IndianaPublished simultaneously in CanadaNo part of this publication may be reproduced, stored in a retrieval system or transmitted in any form orby any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit ted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior writtenpermission of the Publisher, or authorization through payment of the appropriate per-copy fee to theCopyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600.Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing,Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, e-mail:permcoordinator@wiley.com.Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for theRest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related tradedress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the UnitedStates and other countries, and may not be used without written permission. All other trademarks are theproperty of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendormentioned in this book.LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REP RESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CON TENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUTLIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CRE ATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CON TAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THEUNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OROTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF ACOMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THEAUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATIONOR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FUR THER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFOR MATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE.FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVECHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.For general information on our other products and services or to obtain technical support, please contactour Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax317-572-4002.Wiley also publishes its books in a variety of electronic formats. Some content that appears in print maynot be available in electronic books.Library of Congress Control Number: 2004101961ISBN: 0-7645-5589-8Manufactured in the United States of America10 9 8 7 6 5 4 3 2 12B/SU/QT/QU/IN

About the AuthorJanet Valade is the author of PHP 5 For Dummies as well as the first editionof this book. In addition, Janet has authored and revised chapters for Linuxbooks, written chapters for a Certified Internet Webmaster (CIW) book, andwritten the Apache section for a book on LAMP (Linux, Apache, MySQL, andPHP).Janet has 20 years of experience in the computing field. Most recently, sheworked as a Web designer and programmer in a Unix/Linux environment forfour years. Prior to that, Janet worked for 13 years in a university environ ment, where she was a systems analyst. During her tenure, she supervisedthe installation and operation of computing resources, designed and devel oped a data archive, supported faculty and students in their computer usage,wrote numerous technical papers, and developed and presented seminars ona variety of technology topics.To keep in touch, see janet.valade.com.

Author’s AcknowledgmentsFirst, I wish to express my appreciation to the entire open source community.Without those who give their time and talent, there would be no cool PHPand MySQL for me to write about. Furthermore, I never would have learnedthis software without the lists where people generously spend their timeanswering foolish questions from beginners.I want to thank my mother for passing on a writing gene, along with manyother things. And my children always for everything. My thanks to my friendsArt, Dick, and Marge for responding to my last-minute call for help. I particu larly want to thank Sammy, Dude, Spike, Lucky, Upanishad, Sadie, and E.B. fortheir important contributions.And, of course, I want to thank the professionals who make it all possible.Without my agent and the people at Wiley Publishing, Inc., this book wouldnot exist. Because they all do their jobs so well, I can contribute my part tothis joint project.

Publisher’s AcknowledgmentsWe’re proud of this book; please send us your comments through our online registration formlocated at www.dummies.com/register/.Some of the people who helped bring this book to market include the following:Acquisitions, Editorial,and Media DevelopmentProductionSenior Project Editor: Pat O’BrienAcquisitions Editor: Terri VarverisSenior Copy Editor: Teresa ArtmanProject Coordinator: Maridee EnnisLayout and Graphics: Andrea Dahl,Joyce Haughey, Stephanie D. Jumper,Kristin McMullan, Lynsey OsbornEditorial Manager: Kevin KirschnerProofreaders: Andy Hollandbeck,Carl William Pierce, Brian H. Walls,TECHBOOKS Publishing ServicesPermissions Editor: Laura MossIndexer: TECHBOOKS Publishing ServicesTechnical Editor: Craig LukasikMedia Development Specialist: Kit MaloneMedia Development Manager:Laura VanWinkleMedia Development Supervisor:Richard GravesEditorial Assistant: Amanda FoxworthCartoons: Rich Tennant, www.the5thwave.comPublishing and Editorial for Technology DummiesRichard Swadley, Vice President and Executive Group PublisherAndy Cummings, Vice President and PublisherMary C. Corder, Editorial DirectorPublishing for Consumer DummiesDiane Graves Steele, Vice President and PublisherJoyce Pepple, Acquisitions DirectorComposition ServicesGerry Fahey, Vice President of Production ServicesDebbie Stailey, Director of Composition Services

Contents at a GlanceIntroduction .1Part I: Developing a Web Database ApplicationUsing PHP and MySQL .7Chapter 1: Introduction to PHP and MySQL .9Chapter 2: Setting Up Your Work Environment .21Chapter 3: Developing a Web Database Application .37Part II: MySQL Database .63Chapter 4: Building the Database .65Chapter 5: Protecting Your Data .93Part III: PHP .113Chapter 6: General PHP .115Chapter 7: PHP Building Blocks for Programs .145Chapter 8: Data In, Data Out .189Chapter 9: Moving Information from One Web Page to the Next .257Part IV: Applications .279Chapter 10: Putting It All Together .281Chapter 11: Building an Online Catalog .293Chapter 12: Building a Members Only Web Site .331Part V: The Part of Tens .361Chapter 13: Ten Things You Might Want to Do Using PHP Functions .363Chapter 14: Ten PHP Gotchas .371Part VI: Appendixes .377Appendix A: Installing MySQL .379Appendix B: Installing PHP .395Appendix C: Installing and Configuring Apache .409Index .421

Table of ContentsIntroduction .1About This Book .1Conventions Used in This Book .2What You’re Not to Read .3Foolish Assumptions .3How This Book Is Organized .4Part I: Developing a Web Database ApplicationUsing PHP and MySQL .4Part II: MySQL Database .4Part III: PHP .4Part IV: Applications .4Part V: The Part of Tens .5Part VI: Appendixes .5Icons Used in This Book .5Where to Go from Here .5Part I: Developing a Web Database ApplicationUsing PHP and MySQL .7Chapter 1: Introduction to PHP and MySQL . . . . . . . . . . . . . . . . . . . . . . .9What Is a Web Database Application? .10The database .11The application: Moving data in and out of the database .12MySQL, My Database .12Advantages of MySQL .13How MySQL works .14Communicating with the MySQL server .14PHP, a Data Mover .15Advantages of PHP .16How PHP works .16MySQL and PHP, the Perfect Pair .17Advantages of the relationship .18How MySQL and PHP work together .18Keeping Up with PHP and MySQL Changes .19Chapter 2: Setting Up Your Work Environment . . . . . . . . . . . . . . . . . . .21The Required Tools .21Finding a Place to Work .22A company Web site .22A Web hosting company .24Setting up and running your own Web site .27

xiiPHP & MySQL For Dummies, 2nd EditionTesting, Testing, 1, 2, 3 .32Testing PHP .32Testing MySQL .34Chapter 3: Developing a Web Database Application . . . . . . . . . . . . .37Planning Your Web Database Application .37Identifying what you want from the application .38Taking the user into consideration .40Making the site easy to use .41Leaving room for expansion .42Writing it down .42Presenting the Two Running Examples in This Book .42Stuff for Sale .43Members Only .43Designing the Database .44Choosing the data .44Organizing the data .46Designing the Sample Databases .51Pet Catalog design process .51Members Only design process .53Types of Data .56Character data .56Numerical data .57Date and time data .57Enumeration data .57MySQL data type names .58Writing it down .59Taking a Look at the Sample Database Designs .59Stuff for Sale database tables .59Members Only database tables .60Developing the Application .61Building the database .62Writing the programs .62Part II: MySQL Database .63Chapter 4: Building the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65Communicating with MySQL .65Building SQL queries .66Sending SQL queries .67Building a Database .72Creating a new database .73Deleting a database .73Adding tables to a database .73Changing the database structure .76

Table of ContentsMoving Data In and Out of the Database .77Adding information .77Retrieving information .81Combining information from tables .86Updating information .90Removing information .91Chapter 5: Protecting Your Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93Controlling Access to Your Data .93Understanding account names and hostnames .94Finding out about passwords .96Taking a look at account permissions .97Setting Up MySQL Accounts .98Identifying what accounts currently exist .100Adding new accounts and changing permissions .100Adding and changing passwords .102Removing permissions .102Removing accounts .103Backing Up Your Data .104Restoring Your Data .106Repairing tables .107Restoring from a backup copy .108Part III: PHP .114Chapter 6: General PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115Adding a PHP Section to an HTML Page .115Writing PHP Statements .118Using PHP Variables .121Naming a variable .121Creating and assigning values to variables .122Dealing with notices .123Using PHP Constants .124Working with Numbers .125Working with Character Strings .127Single-quoted strings versus double-quoted strings .128Joining strings.130Working with Dates and Times .130Formatting a date .131Storing a timestamp in a variable .132Using dates with MySQL .133Comparing Values .134Making simple comparisons .135Matching character strings to patterns .137Joining Comparisons with and/or/xor .141Adding Comments to Your Program .143xiii