Build your own database driven website using PHP & MySQL

507 Pages·2009·7.342 MB·English

by  Yank, Kevin

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