Build your own database driven website using PHP & MySQL PDF
Preview Build your own database driven website using PHP & MySQL
ALL SOURCE CODE AVAILABLE FOR DOWNLOAD Grey scale CMYK: Pantone: Pantone: Black 50% CMYK O, 53, 100, 0 PANTONE Orange 021 C TEACH YOURSELF PHP & MYSQL CMYK: THE EASY WAY ... Black 100% CMYK 100, 45, 0, 37 PANTONE 2955 C BUILD YOUR OWN P Grey scale Build Your Own Database Driven Web Site Using PHP & MySQL H DATABASE WHAT’S INSIDE? is a practical hands-on guide to learning all the tools, principles, P and techniques needed to build a fully functional database driven & web site using PHP & MySQL. This book covers everything from M DRIVEN WEB SITE installing PHP and MySQL on Windows, Linux, and Mac computers PANTONE Orange 021 C PANTONE 2955 C through to building a live, web-based content management system. Y CMYK O, 53, 100, 0 CMYK 100, 45, 0, 37 S Black 50% Black 100% You’ll learn how to: Q USING PHP & MYSQL Install PHP 5 & MySQL 5 on Windows, Linux, or Mac OS X L Gain a thorough understanding of PHP syntax Detailed installation instructions Master database design principles and SQL BY KEVIN YANK Build a working content management system 4TH EDITION Add, edit, and delete web content without using HTML Build an ecommerce shopping cart Utilize sessions and cookies to track site visitors D Craft SEO-friendly and memorable URLs A Easy-to-understand diagrams T And a whole lot more ... A B A ABOUT KEVIN YANK S Kevin Yank is a world-renowned leader in web development. When not E writing best sellers, Kevin is the Technical Director of sitepoint.com and editor D of the popular SitePoint Tech Times newsletter. Kevin has also co-authored R B Simply JavaScript and Everything You Know About CSS Is Wrong! Practical code examples UIVU SIEIL NND G SITEPOINT BOOKS WEB PROGRAMMING P W Y ISBN: 978-0-9805768-1-8 H O Advocate best practice techniques PEU Lead you through practical examples &BR Provide working code for your web site M S O Y Make learning easy and fun SITW LEARNING PHP & MYSQL HAS NEVER BEEN SO EASY! Q USD $39.95 CAD $49.95 EN L Visit us on the Web at sitepoint.com or for sales and support email [email protected] YANK phpmysql4.indd 1 5/28/2009 5:51:24 PM Summary of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix 1. Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 2. Introducing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 3. Introducing PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 4. Publishing MySQL Data on the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 5. Relational Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 6. Structured PHP Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 7. A Content Management System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197 8. Content Formatting with Regular Expressions . . . . . . . . . . . . . . . . . . . . . . 241 9. Cookies, Sessions, and Access Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261 10. MySQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 11. Advanced SQL Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 12. Binary Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 A. MySQL Syntax Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389 B. MySQL Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415 C. MySQL Column Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 D. PHP Functions for Working with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 449 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463 BUILD YOUR OWN DATABASE DRIVEN WEB SITE USING PHP & MYSQL BY KEVIN YANK 4TH EDITION iv Build Your Own Database Driven Web Site Using PHP & MySQL byKevinYank Copyright©2009SitePointPty.Ltd. Managing Editor:ChrisWyness Editor:KellySteele Technical Editor:AndrewTetlaw Cover Design:AlexWalker Indexer:RussellBrooks Printing History: Latest Update:July2009 1stEd.Aug.2001,2ndEd.Feb.2003, 3rdEd.Oct.2004 FourthEdition:July2009 Notice of Rights Allrightsreserved.Nopartofthisbookmaybereproduced,storedinaretrievalsystemortransmitted inanyformorbyanymeans,withoutthepriorwrittenpermissionofthepublisher,exceptinthecase ofbriefquotationsembodiedincriticalarticlesorreviews. Notice of Liability Theauthorandpublisherhavemadeeveryefforttoensuretheaccuracyoftheinformationherein. However,theinformationcontainedinthisbookissoldwithoutwarranty,eitherexpressorimplied. NeithertheauthorsandSitePointPty.Ltd.,noritsdealersordistributorswillbeheldliableforany damagestobecausedeitherdirectlyorindirectlybytheinstructionscontainedinthisbook,orbythe softwareorhardwareproductsdescribedherein. Trademark Notice Ratherthanindicatingeveryoccurrenceofatrademarkednameassuch,thisbookusesthenamesonly inaneditorialfashionandtothebenefitofthetrademarkownerwithnointentionofinfringementof thetrademark. PublishedbySitePointPty.Ltd. 48CambridgeStreetCollingwood VICAustralia3066. Web:www.sitepoint.com Email:[email protected] ISBN978-0-9805768-1-8 PrintedandboundintheUnitedStatesofAmerica v About the Author AsTechnicalDirectorforSitePoint,KevinYankkeepsabreastofallthatisnewandexciting inwebtechnology.Bestknownforthebookyouarereadingrightnow,healsoco-authored SimplyJavaScript(http://www.sitepoint.com/books/javascript1/)withCameronAdamsand EverythingYouKnowAboutCSSIsWrong!(http://www.sitepoint.com/books/csswrong1/) withRachelAndrew.Inaddition,KevinhoststheSitePointPodcast (http://www.sitepoint.com/podcast/)andwritestheSitePointTechTimes,afreeemail newsletterthatgoesouttoover240,000subscribersworldwide. KevinlivesinMelbourne,Australiaandenjoysspeakingatconferences,aswellasvisiting friendsandfamilyinCanada.He’salsopassionateaboutperformingimprovisedcomedy theaterwithImproMelbourne(http://www.impromelbourne.com.au/)andflyinglightaircraft. Kevin’spersonalblogisYes,I’mCanadian(http://yesimcanadian.com/). About the Technical Editor AndrewTetlawhasbeentinkeringwithwebsitesasawebdevelopersince1997.AtSitePoint heisdedicatedtomakingtheworldabetterplacethroughthetechnicaleditingofSitePoint books,kits,articles,andnewsletters.Heisalsoabusyfatheroffive,enjoyscoffee,andoften neglectshisblogathttp://tetlaw.id.au/. About SitePoint SitePointspecializesinpublishingfun,practical,andeasy-to-understandcontentforWeb professionals.Visithttp://www.sitepoint.com/toaccessourblogs,books,newsletters,articles, andcommunityforums. Tomyparents,Cheryland Richard,formakingallthis possible. Table of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Who Should Read This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx What’s in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Where to Find Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv The SitePoint Forums . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv The Book’s Web Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv The SitePoint Newsletters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Your Feedback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Conventions Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvi Code Samples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvi Tips, Notes, and Warnings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii Chapter 1 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Your Own Web Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Windows Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 All-in-one Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Installing Individual Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Mac OS X Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 All-in-one Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Installing Individual Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Linux Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Installing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Installing PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Post-Installation Set-up Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 What to Ask Your Web Host . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Your First PHP Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Full Toolbox, Dirty Hands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 x Chapter 2 Introducing MySQL . . . . . . . . . . . . . . . . . . . . . 53 An Introduction to Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Logging On to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Structured Query Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Inserting Data into a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Viewing Stored Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Modifying Stored Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Deleting Stored Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Let PHP Do the Typing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Chapter 3 Introducing PHP . . . . . . . . . . . . . . . . . . . . . . . . . 73 Basic Syntax and Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Variables, Operators, and Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 User Interaction and Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Control Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Hiding the Seams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Avoid Advertising Your Technology Choices . . . . . . . . . . . . . . . . . . 104 Use PHP Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Many Templates, One Controller . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Bring On the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Chapter 4 Publishing MySQL Data on the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 The Big Picture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Connecting to MySQL with PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Sending SQL Queries with PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
The list of books you might like
