PHP 6 and MySQL 5 for dynamic Web sites

635 Pages·2008·23.54 MB·English

by  Larry E 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
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
Introduction
What Are Dynamic ◆ Often have interfaces where administra-
tors can manage the site’s content
Web Sites?
◆ Are easierto maintain, upgrade, and
Dynamic Web sites are flexible and potent build upon than statically made sites
creatures, more accurately described as
There are many technologies available for
applicationsthan merely sites. Dynamic
creating dynamic Web sites. The most com-
Web sites
mon are ASP.NET (Active Server Pages, a
◆ Respond to different parameters (for Microsoft construct), JSP (Java Server Pages),
example, the time of day or the version of ColdFusion, Ruby on Rails, and PHP. Dynamic
the visitor’s Web browser) Web sites don’t always rely on a database,
but more and more of them do, particularly
◆ Have a “memory,” allowing for user regis-
as excellent databaseapplications like
tration and login, e-commerce, and simi-
MySQL are available at little to no cost.
lar processes
◆ Almost always have HTML forms, so that
? people can perform searches, provide
s
e feedback, and so forth
t
i
S
b
e
W
c
i
m
a
n
y
D
e
r
A
t
a
h
W
Figure i.1 The home page for PHP.
x
Introduction
What is PHP? Also, PHP is a scripting language, as
opposed to a programming language: PHP was
PHP originally stood for “Personal Home designed to write Web scripts, not stand-
Page” as it was created in 1994 by Rasmus alone applications (although, with some extra
Lerdorf to track the visitors to his online effort, you can now create applications in
résumé. As its usefulness and capabilities PHP). PHP scripts run only after an event
grew (and as it started being used in more occurs—for example, when a user submits
professional situations), it came to mean aform or goes to a URL.
“PHP: Hypertext Preprocessor.”
I should add to this definition that PHP is
According to the official PHP Web site, aserver-side,cross-platformtechnology, both
found at www.php.net(Figure i.1), PHP is a descriptionsbeing important. Server-side
“widely-used general-purpose scripting lan- refers to the fact that everything PHP does
guage thatis especially suited for Web devel- occurs on the server. AWeb server applica-
opment and can be embedded into HTML.” tion, like Apache or Microsoft’s IIS (Internet
It’s a long but descriptive definition, whose Information Services), is required and all
meaning I’ll explain. PHP scripts must be accessed through a
W
Starting at the end of that statement, to say URL (http://-something). Its cross-platform h
a
that PHP can be embedded into HTML means nature means that PHP runs on most oper- t
ating systems,including Windows, Unix A
that you can take a standard HTML page, r
drop in some PHP wherever you need it, and (and its many variants), and Macintosh. e
More important, the PHP scripts written on D
end up with a dynamic result. This attribute y
one server will normally work onanother n
makes PHP very approachable for anyone a
that’s done even a little bit of HTML work. with little or no modification. m
i
c
At the timethe book was written, PHP was W
at version 5.2.4, with version 4.4.7 still being e
b
maintained. Support for version 4 is being S
dropped, though, and it’s recommended that i
t
everyone use at least version 5 of PHP. This e
s
edition of this book actually focuses on ver- ?
sion 6 of PHP, to be released in late 2007 or
in 2008. If you’re still using version 4, you
really should upgrade. If that’s not in your
plans, then please grab the second edition of
this book instead. If you’re using PHP 5,
either the second or this edition of the book
will work for you. In this edition, I will make
it clear which features and functions are
PHP 6–specific.
xi
Introduction
What’s new in PHP6 Why use PHP?
Because of the planned extinction of PHP 4, Put simply, when it comes to developing
many users and Web hosting companies will dynamic Web sites, PHP is better, faster, and
likely make a quick transition from PHP 4 to easier to learn than the alternatives. What
PHP 5 to PHP 6. To discuss what’s new in you get with PHP is excellent performance,
PHP 6, I’ll start with the even bigger differ- atight integration withnearly every database
ences between PHP 4 and 5. available, stability, portability, and a nearly
limitless feature set due to its extendibility.
PHP 5, like PHP 4 before it, is a major new
All of this comes at no cost (PHP is open
development of this popular programming
source) and with a very manageable learning
language. The most critical changes in PHP 5
curve. PHP is one of the best marriages I’ve
involve object-oriented programming
ever seen between the ease with which
(OOP).Those changes don’t really impact
beginning programmers can start using it
this book, as OOP isn’t covered (I do so in
and the ability for more advanced program-
my book PHP 5 Advanced: Visual QuickPro
mers to do everything they require.
Guide). With respect to this book, the
?
s biggest change in PHP 5 is the addition of Finally, the proof is in the pudding: PHP has
e
t the Improved MySQL Extension, which is seen an exponential growth in use since its
i
S used to communicate with MySQL. The inception, overtaking ASP as the most pop-
b
e Improved MySQL Extension offers many ular scripting language being used today. It’s
W
benefits over the older MySQL extension the most requested module for Apache (the
c
i and will be used exclusively. most-used Web server), and by the time this
m
book hits the shelves, PHP will be on nearly
a The big change in PHP 6 is support for
n 25 million domains.
y Unicode, which is to say that PHP can now
D
e handle characters in every language in the Of course, you might assume that I, as the
r world. This is huge, and it’s also one of the author of a book on PHP (several, actually),
A
t reasons it’s taken a while to release PHP 6. have a biased opinion.Although not nearly
a
h What this means in terms of programming to the same extent as PHP, I’ve also devel-
W
is covered in Chapter 14, “Making Universal oped sites using Java Server Pages (JSP),
Sites.” The information in that chapter is Ruby on Rails (RoR), and ASP.NET. Each has
also used in Chapter 15, “Example—Message its pluses and minuses, but PHP is the tech-
Board.” Beyond Unicode support, PHP 6 cleans nology I always return to. You might hear
up a lot of garbage that was left in PHP 5 even that it doesn’t perform or scale as well as
though the recommendation was not to use other technologies, but Yahoo! handles over
such things. The two biggest removals are the 3.5 billion hits per day using PHP (yes, billion).
“Magic Quotes” and “register globals” features. You might also wonder how secure PHP is.
But security isn’t in the language; it’s in how
that language is used. Rest assured that
acomplete and up-to-date discussion of all
the relevant security concerns is provided
bythis book!
xii