PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide

641 Pages·2007·12.434 MB·English

by  Larry Ullman

VISUAL QUICKPRO GUIDE
PHP 6
M SQL 5
AND Y
FOR DYNAMIC WEB SITES
Larry Ullman
Peachpit Press
Visual QuickPro Guide
PHP6 and MySQL5 for Dynamic Web Sites
Larry Ullman
Peachpit Press
1249 Eighth Street
Berkeley, CA94710
510/524-2178
510/524-2221 (fax)
Find us on the Web at: www.peachpit.com
To report errors, please send a note to: [email protected]
Peachpit Press isa division of Pearson Education.
Copyright © 2008 by Larry Ullman
Editor: Rebecca Gulick
Copy Editor: Bob Campbell
Production Coordinator: Becky Winter
Compositors: Myrna Vladic, Jerry Ballew, and Rick Gordon
Indexer: Rebecca Plunkett
Cover Production: Louisa Adair
Technical Reviewer: Arpad Ray
Notice of rights
All rights reserved. No part of this book may be reproduced or transmitted in any form by any means, elec-
tronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the pub-
lisher. For information on getting permission for reprints and excerpts, contact [email protected].
Notice of liability
The information in this book is distributed on an “As Is” basis, without warranty. While every precaution
has been taken in the preparation of the book, neither the author nor Peachpit Press shall have any liability
to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indi-
rectly by the instructions contained in this book or by the computersoftware and hardware products
described in it.
Trademarks
MySQL is a registered trademark of MySQL AB in the United States and in other countries. Macintosh and
Mac OS X are registered trademarks of Apple Computer, Inc. Microsoft and Windows are registered trade-
marks of Microsoft Corporation. Other product names used in this book may be trademarks of their own
respective owners. Images of Web sites in this book are copyrighted by the original holders and are used
with their kind permission. This book is not officially endorsed by nor affiliated with any of the above com-
panies, including MySQL AB.
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as
trademarks. Where those designations appear in this book, and Peachpit was aware of a trademark claim,
the designations appear as requested by the owner of the trademark. All other product names and services
identified throughout this book are used in editorial fashion only and for the benefit of such companies
with no intention of infringement of the trademark. No such use, or the use of any trade name, is intended
to convey endorsement or other affiliation with this book.
ISBN-13: 978-0-321-52599-4
ISBN-10:0-321-52599-X
9 8 7 6 5 4 3 2 1
Printed and bound in the United States of America
Dedication
Dedicated to the fine faculty at my alma
mater, Northeast Missouri State University.
In particular, I would like to thank: Dr. Monica
Barron, Dr. Dennis Leavens, Dr. Ed Tyler, and
Dr. Cole Woodcox, whom I also have the
pleasure of calling my friend. I would not be
who I am as a writer, as a student, as a
teacher, or as a person if it were not for the
magnanimous, affecting, and brilliant
instruction I received from these educators.
Special Thanks to:
My heartfelt thanks to everyone at Peachpit
Press, as always.
Mygratitude to editor extraordinaire Rebecca
Gulick, who makes my job so much easier.
And thanks to Bob Campbell for his hard
work, helpful suggestions, and impressive
attention to detail. Thanks also to Rebecca
Plunkett for indexingand Becky Winter,
Myrna Vladic, Jerry Ballew, and Rick Gordon
for laying out the book, and thanks to Arpad
Ray for his technical review.
Kudos to the good people working on PHP,
MySQL, Apache, phpMyAdmin, and XAMPP,
among other great projects. And a hearty
“cheers” to the denizens of the various news-
groups, mailing lists, support forums, etc., who
offer assistance and advice to those in need.
Thanks, as always, to the readers, whose sup-
port gives my job relevance. An extra helping
of thanks to those who provided the transla-
tions in Chapter 15, “Example—Message
Board,” and who offered up recommendations
as to what they’d like to see in this edition.
Thanks to Nicole and Christina for enter-
taining and taking care of the kids so that
Icould get some work done.
Finally, I would not be able to get through
asingle book if it weren’t for the love and
support of my wife, Jessica. And a special
shout out to Zoe and Sam, who give me rea-
sons to, and not to, write books!
Table of Contents
Introduction: ix
What Are Dynamic Web Sites? . . . . . . . . . . . . . . . . x
What You’ll Need . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi
About This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Companion Web Site . . . . . . . . . . . . . . . . . . . . . . . . xix
Chapter 1: Introduction to PHP 1
T
Basic Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 a
b
Sending Data to the l
e
Web Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 o
Writing Comments . . . . . . . . . . . . . . . . . . . . . . . . . . 10 f
C
What Are Variables? . . . . . . . . . . . . . . . . . . . . . . . . . 14 o
n
Introducing Strings . . . . . . . . . . . . . . . . . . . . . . . . . . 18 t
e
Concatenating Strings . . . . . . . . . . . . . . . . . . . . . . . 21 n
Introducing Numbers . . . . . . . . . . . . . . . . . . . . . . . . 23 ts
Introducing Constants . . . . . . . . . . . . . . . . . . . . . . . 27
Single vs. Double Quotation Marks . . . . . . . . . . . . 30
Chapter 2: Programming with PHP 33
Creating an HTML Form . . . . . . . . . . . . . . . . . . . . . 34
Handling an HTML Form . . . . . . . . . . . . . . . . . . . . 38
Conditionals and Operators . . . . . . . . . . . . . . . . . . 42
Validating Form Data . . . . . . . . . . . . . . . . . . . . . . . . 46
Introducing Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . 52
For and While Loops . . . . . . . . . . . . . . . . . . . . . . . . 70
Chapter 3: Creating Dynamic Web Sites 73
Including Multiple Files . . . . . . . . . . . . . . . . . . . . . . 74
Handling HTML Forms, Revisited . . . . . . . . . . . . . 84
Making Sticky Forms . . . . . . . . . . . . . . . . . . . . . . . . 89
Creating Your Own Functions . . . . . . . . . . . . . . . . 92
Chapter 4: Introduction to MySQL 107
Naming Database Elements . . . . . . . . . . . . . . . . . 108
Choosing Your Column Types . . . . . . . . . . . . . . . 110
Choosing Other Column Properties . . . . . . . . . . 114
Accessing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 116
v
Table of Contents
Chapter 5: Introduction to SQL 123
Creating Databases and Tables . . . . . . . . . . . . . . . 124
Inserting Records . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Selecting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Using Conditionals . . . . . . . . . . . . . . . . . . . . . . . . . 133
Using LIKE and NOT LIKE . . . . . . . . . . . . . . . . . . 136
Sorting Query Results . . . . . . . . . . . . . . . . . . . . . . . 138
Limiting Query Results . . . . . . . . . . . . . . . . . . . . . 140
Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Deleting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Using Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Chapter 6: Advanced SQLand MySQL 157
Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Performing Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Grouping Selected Results . . . . . . . . . . . . . . . . . . . 178
s
t Creating Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
n
e Using Different Table Types . . . . . . . . . . . . . . . . . 185
t
n Performing FULLTEXT Searches . . . . . . . . . . . . 188
o
Performing Transactions . . . . . . . . . . . . . . . . . . . . 194
C
f
o Chapter 7: Error Handling and Debugging 199
e
l Error Types and Basic Debugging . . . . . . . . . . . . 200
b
a Displaying PHP Errors . . . . . . . . . . . . . . . . . . . . . . 206
T
Adjusting Error Reporting in PHP . . . . . . . . . . . . 208
Creating Custom Error Handlers . . . . . . . . . . . . . 211
PHP Debugging Techniques . . . . . . . . . . . . . . . . . 216
SQL and MySQL Debugging Techniques . . . . . . 220
Chapter 8: Using PHPwith MySQL 223
Modifying the Template . . . . . . . . . . . . . . . . . . . . . 224
Connecting to MySQL . . . . . . . . . . . . . . . . . . . . . . 226
Executing Simple Queries . . . . . . . . . . . . . . . . . . . 230
Retrieving Query Results . . . . . . . . . . . . . . . . . . . . 239
Ensuring Secure SQL . . . . . . . . . . . . . . . . . . . . . . . 243
Counting Returned Records . . . . . . . . . . . . . . . . . 249
Updating Records withPHP . . . . . . . . . . . . . . . . . 251
Chapter 9: Common Programming Techniques 259
Sending Values to a Script . . . . . . . . . . . . . . . . . . . 260
Using Hidden Form Inputs . . . . . . . . . . . . . . . . . . 264
Editing Existing Records . . . . . . . . . . . . . . . . . . . . 270
Paginating Query Results . . . . . . . . . . . . . . . . . . . . 277
Making Sortable Displays . . . . . . . . . . . . . . . . . . . 285
vi
Table of Contents
Chapter 10: Web Application Development 291
Sending Email . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Date and Time Functions . . . . . . . . . . . . . . . . . . . 298
Handling File Uploads . . . . . . . . . . . . . . . . . . . . . . 302
PHP and JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . 315
Understanding HTTP Headers . . . . . . . . . . . . . . . 322
Chapter 11: Cookies and Sessions 327
Making a Login Page . . . . . . . . . . . . . . . . . . . . . . . 328
Making the Login Functions . . . . . . . . . . . . . . . . 331
Using Cookies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Using Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Improving Session Security . . . . . . . . . . . . . . . . . . 358
Chapter 12: Security Methods 361
Preventing Spam . . . . . . . . . . . . . . . . . . . . . . . . . . . 362
Validating Data by Type . . . . . . . . . . . . . . . . . . . . . 369 T
a
Preventing XSS Attacks . . . . . . . . . . . . . . . . . . . . . 374 b
l
Preventing SQL Injection Attacks . . . . . . . . . . . . 377 e
o
Database Encryption . . . . . . . . . . . . . . . . . . . . . . . 383 f
C
Chapter 13: Perl-Compatible o
n
Regular Expressions 389 t
e
n
Creating a Test Script . . . . . . . . . . . . . . . . . . . . . . . 390
t
s
Defining Simple Patterns . . . . . . . . . . . . . . . . . . . . 394
Using Quantifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Using Character Classes . . . . . . . . . . . . . . . . . . . . . 400
Finding All Matches . . . . . . . . . . . . . . . . . . . . . . . . 403
Using Modifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Matching and Replacing Patterns . . . . . . . . . . . . 409
Chapter 14: Making Universal Sites 413
Character Sets and Encoding . . . . . . . . . . . . . . . . 414
Creating Multilingual Web Pages . . . . . . . . . . . . . 416
Unicode in PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Collation in PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . 424
Transliteration in PHP . . . . . . . . . . . . . . . . . . . . . . 427
Languages and MySQL . . . . . . . . . . . . . . . . . . . . . 430
Time Zones and MySQL . . . . . . . . . . . . . . . . . . . . 434
Working with Locales . . . . . . . . . . . . . . . . . . . . . . . 437
vii
Table of Contents
Chapter 15: Example—Message Board 441
Making the Database . . . . . . . . . . . . . . . . . . . . . . . 442
Writing the Templates . . . . . . . . . . . . . . . . . . . . . . 451
Creating the IndexPage . . . . . . . . . . . . . . . . . . . . . 460
Creating the Forum Page . . . . . . . . . . . . . . . . . . . . 461
Creating the Thread Page . . . . . . . . . . . . . . . . . . . 466
Posting Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Chapter 16: Example—User Registration 483
Creating the Templates . . . . . . . . . . . . . . . . . . . . . 484
Writing the Configuration Scripts . . . . . . . . . . . . 490
Creating the Home Page . . . . . . . . . . . . . . . . . . . . 498
Registration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
Activating an Account . . . . . . . . . . . . . . . . . . . . . . 509
Logging In and Logging Out . . . . . . . . . . . . . . . . . 513
Password Management . . . . . . . . . . . . . . . . . . . . . 519
s
t
n Chapter 17: Example—E-Commerce 529
e
t Creating the Database . . . . . . . . . . . . . . . . . . . . . . 530
n
o The Administrative Side . . . . . . . . . . . . . . . . . . . . 536
C
f Creating the Public Template . . . . . . . . . . . . . . . . 553
o The Product Catalog . . . . . . . . . . . . . . . . . . . . . . . . 557
e
l The Shopping Cart . . . . . . . . . . . . . . . . . . . . . . . . . 569
b
a Recording the Orders . . . . . . . . . . . . . . . . . . . . . . . 579
T
Appendix A: Installation 587
Installation on Windows . . . . . . . . . . . . . . . . . . . . 588
Installation on Mac OS X . . . . . . . . . . . . . . . . . . . 591
MySQL Permissions . . . . . . . . . . . . . . . . . . . . . . . . 594
Testing Your Installation . . . . . . . . . . . . . . . . . . . . 598
Configuring PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . 601
Index 603
viii
i
Introduction
Today’s Web users expect exciting pages that are updated frequently and provide a
customized experience. For them, Web sites are more like communities, to which
they’ll return time and again. At the same time, Web site administrators want sites
that are easier to update and maintain, understanding that’s the only real way to
keep up with visitors’ expectations. For these reasons and more, PHP and MySQL
have become the de facto standards for creating dynamic, database-driven Web sites.
This book represents the culmination of my many years of Web development experi-
ence coupled with the value of having written several previous books on the technologies
I
n
discussed herein. The focus of this book is on covering the most important knowledge t
r
in the most efficient manner. It will teach you how to begin developing dynamic Web o
d
sites and give you plenty of example code to get you started. All you need to provide u
c
is an eagerness to learn. t
i
o
n
Well, that and a computer.
ix