MySQL/PHP Database Applications

622 Pages·2001·3.517 MB·English

by  Jay Greenspan, Brad Bulger

3537-4 FM.f.qc 12/15/00 15:31 Page i
MySQL/PHP Database
Applications
3537-4 FM.f.qc 12/15/00 15:31 Page ii
3537-4 FM.f.qc 12/15/00 15:31 Page iii
MySQL/PHP Database
Applications
Jay Greenspan and Brad Bulger
M&T Books
An imprint of IDGBooks Worldwide, Inc.
Foster City, CA (cid:2) Chicago, IL (cid:2) Indianapolis, IN (cid:2) New York, NY
3537-4 FM.f.qc 12/15/00 15:31 Page iv
MySQL/PHP Database Applications Service department at 800-762-2974. For reseller
Published by information, including discounts and premium sales,
M&T Books please call our Reseller Customer Service department
An imprint of IDG Books Worldwide, Inc. at 800-434-3422.
919 E. Hillsdale Blvd., Suite 400 For information on where to purchase IDG Books
Foster City, CA 94404 Worldwide’s books outside the U.S., please contact our
www.idgbooks.com(IDG Books Worldwide Web site) International Sales department at 317-572-3993 or fax
Copyright ©2001 IDG Books Worldwide, Inc. All rights 317-572-4002.
reserved. No part of this book, including interior design, For consumer information on foreign language
cover design, and icons, may be reproduced or translations, please contact our Customer Service
transmitted in any form, by any means (electronic, department at 800-434-3422, fax [phone], or
photocopying, recording, or otherwise) without the prior e-mail [email protected].
written permission of the publisher. For information on licensing foreign or domestic rights,
ISBN: 0-7645-3537-4 please phone [phone].
Printed in the United States of America For sales inquiries and special prices for bulk quantities,
10 9 8 7 6 5 4 3 2 1 please contact our Order Services department at
800-434-3422 or write to the address above.
1O/QZ/QR/QR/FC
For information on using IDG Books Worldwide’s books
Distributed in the United States by IDG Books
in the classroom or for ordering examination copies,
Worldwide, Inc.
please contact our Educational Sales department at
Distributed by CDG Books Canada Inc. for Canada; by 800-434-2086 or fax [phone].
Transworld Publishers Limited in the United Kingdom; by
For press review copies, author interviews, or other
IDG Norge Books for Norway; by IDG Sweden Books for
publicity information, please contact our Public Relations
Sweden; by IDG Books Australia Publishing Corporation
department at 650-653-7000 or fax [phone].
Pty. Ltd. for Australia and New Zealand; by TransQuest
Publishers Pte Ltd. for Singapore, Malaysia, Thailand, For authorization to photocopy items for corporate,
Indonesia, and Hong Kong; by Gotop Information Inc. personal, or educational use, please contact Copyright
for Taiwan; by ICG Muse, Inc. for Japan; by Intersoft Clearance Center, 222 Rosewood Drive, Danvers, MA
or South Africa; by Eyrolles for France; by International 01923, or fax [phone].
Thomson Publishing for Germany, Austria, and Library of Congress Cataloging-in-Publication Data
Switzerland; by Distribuidora Cuspide for Argentina; by Greenspan, Jay, 1968-
LR International for Brazil; by Galileo Libros for Chile; by My SQL/PHP database applications / Jay Greenspan
Ediciones ZETA S.C.R. Ltda. for Peru; by WS Computer and Brad Bulger.
Publishing Corporation, Inc., for the Philippines; by p. cm.
Contemporanea de Ediciones for Venezuela; by Express ISBN 0-7645-3537-4 (alk. paper)
Computer Distributors for the Caribbean and West Indies; 1. SQL (Computer program language) 2. PHP
by Micronesia Media Distributor, Inc. for Micronesia; by (Computer program language 3.Web databases.
Chips Computadoras S.A. de C.V. for Mexico; by Editorial I. Bulger, Brad, 1959- II. Title.
Norma de Panama S.A. for Panama; by American QA76.73.S67G73 2001
Bookshops for Finland. 005.13’3--dc21 00-053995
For general information on IDG Books Worldwide’s
books in the U.S., please call our Consumer Customer
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND AUTHOR HAVE USED THEIR BEST
EFFORTS IN PREPARING THIS BOOK. THE PUBLISHER AND AUTHOR MAKE NO REPRESENTATIONS OR
WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS BOOK AND
SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR
PURPOSE. THERE ARE NO WARRANTIES WHICH EXTEND BEYOND THE DESCRIPTIONS CONTAINED IN THIS
PARAGRAPH. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES OR WRITTEN
SALES MATERIALS. THE ACCURACY AND COMPLETENESS OF THE INFORMATION PROVIDED HEREIN AND THE
OPINIONS STATED HEREIN ARE NOT GUARANTEED OR WARRANTED TO PRODUCE ANY PARTICULAR RESULTS,
AND THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY INDIVIDUAL.
NEITHER THE PUBLISHER NOR AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER
COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER
DAMAGES.
Trademarks:All brand names and product names used in this book are trade names, service marks, trademarks, or
registered trademarks of their respective owners. IDG Books Worldwide is not associated with any product or vendor
mentioned in this book.
is a registered trademark or trademark is a trademark of
under exclusive license to IDG Books Worldwide, Inc. IDG Books Worldwide, Inc.
from International Data Group, Inc. in the
United States and/or other countries.
3537-4 FM.f.qc 12/15/00 15:31 Page v
Welcome to the world of IDG Books Worldwide.
IDG Books Worldwide, Inc., is a subsidiary of International Data Group, the world’s largest publisher of
computer-related information and the leading global provider of information services on information technology.
IDG was founded more than 30 years ago by Patrick J. McGovern and now employs more than 9,000 people
worldwide. IDG publishes more than 290 computer publications in over 75 countries. More than 90 million
people read one or more IDG publications each month.
Launched in 1990, IDG Books Worldwide is today the #1 publisher of best-selling computer books in the
United States. We are proud to have received eight awards from the Computer Press Association in recognition
of editorial excellence and three from Computer Currents’ First Annual Readers’ Choice Awards. Our best-
selling ...For Dummies® series has more than 50 million copies in print with translations in 31 languages. IDG
Books Worldwide, through a joint venture with IDG’s Hi-Tech Beijing, became the first U.S. publisher to
publish a computer book in the People’s Republic of China. In record time, IDG Books Worldwide has become
the first choice for millions of readers around the world who want to learn how to better manage their
businesses.
Our mission is simple: Every one of our books is designed to bring extra value and skill-building instructions
to the reader. Our books are written by experts who understand and care about our readers. The knowledge
base of our editorial staff comes from years of experience in publishing, education, and journalism —
experience we use to produce books to carry us into the new millennium. In short, we care about books, so
we attract the best people. We devote special attention to details such as audience, interior design, use of
icons, and illustrations. And because we use an efficient process of authoring, editing, and desktop publishing
our books electronically, we can spend more time ensuring superior content and less time on the technicalities
of making books.
You can count on our commitment to deliver high-quality books at competitive prices on topics you want
to read about. At IDG Books Worldwide, we continue in the IDG tradition of delivering quality for more than
30 years. You’ll find no better book on a subject than one from IDG Books Worldwide.
John Kilcullen
Chairman and CEO
IDG Books Worldwide, Inc.
Eighth Annual Eleventh Annual
Computer Press Computer Press
Awards 1992 Ninth Annual Tenth Annual Awards 1995
Computer Press Computer Press
Awards 1993 Awards 1994
IDG is the world’s leading IT media, research and exposition company. Founded in 1964, IDG had 1997 revenues of $2.05
billion and has more than 9,000 employees worldwide. IDG offers the widest range of media options that reach IT buyers
in 75 countries representing 95% of worldwide IT spending. IDG’s diverse product and services portfolio spans six key areas
including print publishing, online publishing, expositions and conferences, market research, education and training, and
global marketing services. More than 90 million people read one or more of IDG’s 290 magazines and newspapers, including
IDG’s leading global brands — Computerworld, PC World, Network World, Macworld and the Channel World family of
publications. IDG Books Worldwide is one of the fastest-growing computer book publishers in the world, with more than
700 titles in 36 languages. The “...For Dummies®” series alone has more than 50 million copies in print. IDG offers online
users the largest network of technology-specific Web sites around the world through IDG.net (http://www.idg.net), which
comprises more than 225 targeted Web sites in 55 countries worldwide. International Data Corporation (IDC) is the world’s
largest provider of information technology data, analysis and consulting, with research centers in over 41 countries and more
than 400 research analysts worldwide. IDG World Expo is a leading producer of more than 168 globally branded conferences
and expositions in 35 countries including E3 (Electronic Entertainment Expo), Macworld Expo, ComNet, Windows World
Expo, ICE (Internet Commerce Expo), Agenda, DEMO, and Spotlight. IDG’s training subsidiary, ExecuTrain, is the world’s
largest computer training company, with more than 230 locations worldwide and 785 training courses. IDG Marketing
Services helps industry-leading IT companies build international brand recognition by developing global integrated marketing
programs via IDG’s print, online and exposition products worldwide. Further information about the company can be found
at www.idg.com. 1/26/00
3537-4 FM.f.qc 12/15/00 15:31 Page vi
Credits
QUALITY CONTROL TECHNICIAN
ACQUISITIONS EDITOR Dina F Quan
Debra Williams Cauley
PERMISSIONS EDITOR
PROJECT EDITOR Laura Moss
Neil Romanosky
MEDIA DEVELOPMENT SPECIALIST
TECHNICAL EDITORS Angela Denny
Richard Lynch
Michael Widenius MEDIA DEVELOPMENT COORDINATOR
Marisa Pearman
COPY EDITOR
S. B. Kleinman BOOK DESIGNER
Jim Donohue
PROJECT COORDINATORS
Louigene A. Santos ILLUSTRATORS
Danette Nurse Gabriele McCann
Ronald Terry
GRAPHICS AND PRODUCTION
SPECIALISTS PROOFREADING AND INDEXING
Robert Bilhmayer York Production Services
Rolly Delrosario
Jude Levinson COVER IMAGE
Michael Lewis ©Noma/Images.com
Ramses Ramirez
Victor Pérez-Varela
About the Authors
Jay Greenspan made his living as a technical consultant and editor before finding
his way into Wired Digital’s Webmonkey. There he learned everything he knows
about Web technology and gained an appreciation for electronic music, the color
orange, and a “cute top.” He now makes his living as a writer and consultant. He
will neither confirm nor deny the rumors that he once worked for a prime-time
game show.
Brad Bulgercan remember when computers were as big as refrigerators and old-
timers would come into the machine room and call them “mini.” He learned more
than anyone really should about database systems by working for Relational
Technology nee Ingres nee CA for many years. After an interregnum, he got a job
with Wired. He would still like to know when the future is going to get here, but has
a sneaking suspicion he already knows.
3537-4 FM.f.qc 12/15/00 15:31 Page vii
In memory of Dr. Jonathan B. Postel
3537-4 FM.f.qc 12/15/00 15:31 Page ix
Preface
Welcome. If you are thumbing through these pages, you’re probably considering
writing Web-based applications with PHP and MySQL. If you decide to go with
these tools, you’ll be in excellent company. Thousands of developers—from total
newbies to programmers with years of experience—are turning to PHP and MySQL
for their Web-based projects; and for good reason.
Both PHP and MySQL are easy to use, fast, free, and powerful. If you want to get
a dynamic Web site up quickly, there are no better choices. The PHP scripting lan-
guage was built for the Web. All the tasks common to Web development can be
accomplished in PHP with an absolute minimum of effort. Similarly, MySQL excels at
tasks common to dynamic Web sites. Whether you’re creating a content-management
system or an e-commerce application, MySQL is a great choice for your data storage.
Is This Book for You?
There are quite a few books that deal with PHP and a few that cover MySQL. We’ve
read some of these and found a few to be quite helpful. If you’re looking for a book
that deals with gory details of either of these packages, you should probably look
elsewhere.
The focus of this book is applications development. We are concerned with what
it takes to get data-driven Web sites up and running in an organized and efficient
way. The book does not go into arcane detail of every aspect of either of these tools.
For example, in this book, you will not find a discussion of PHP’s LDAP functions
or MySQL’s C application program interface (API). Instead, we will focus on the
pieces of both packages that affect one another. We hope that by the time you’re
done with this book you’ll know what it takes to get an application up and running
using PHP and MySQL.
How This Book Is Organized
We have organized the book into four parts.
Part I: Using MySQL
Before you code any PHP scripts, you will need to know how to design a database,
create tables in your database, and get the information you want from the database.
Part I of this book will show you about all you need to know to work with MySQL.
ix
3537-4 FM.f.qc 12/15/00 15:31 Page x
x Preface
Part II: Using PHP
As an applications developer, the bulk of your time will be spent writing scripts that
access the database and present HTML to a user’s browser. Part II will start by
showing you the basics of the PHP scripting language, covering how PHP works
with variables, conditions, and control structures. Part II will also cover many of
PHP’s functions and discuss techniques for writing clean, manageable code.
Part III: Simple Applications
In this part, we present two of the seven applications in this book: a guestbook and
a survey. Here you will see the lessons from Parts I and II put into practice as we
build working applications.
Part IV: Not So Simple Applications
Here the applications will be more complex, as we present applications commonly
used on the Web. You will see how you can design a content management system,
a discussion board, a shopping cart, and other useful applications. Along the way,
we will show some tips and techniques that should be helpful as you write your
applications.
Part V: Appendixes
The appendixes cover several topics of interest to the MySQL/PHP developer. In the
appendixes, you will find installation and configuration instructions, quick refer-
ence guides to PHP and MySQL functions, a regular expressions overview, and
guides to MySQL administration. In addition, there are a few helpful resources,
snippets of code, and instructions on using the CD-ROM.
Tell Us What You Think
Both the publisher and authors of this book hope you find it a valuable resource.
Please feel free to register this book at the IDG Books Web site (http://www.
idgbooks.com) and give us your feedback. Also check in at the site we’ve dedicated
to this book, http://www.mysqlphpapps.com/, where you will be able to contact
the authors and find updates to the applications created for this book.
3537-4 FM.f.qc 12/15/00 15:31 Page xi
Acknowledgments
This book would never have happened if not for the efforts of Debra Williams
Cauley. I thank her for her patience and persistence. The efforts and talents of Neil
Romanosky, S. B. Kleinman, and many others at IDG Books have made this book
more lucid and attractive than we could have hoped. Richard Lynch’s exacting eye
and technical acumen kept our code clean, fast, and readable.
Any book on open-source software owes debt to those who have created these
great tools. So I thank everyone involved with PHP and MySQL, from the core
developers to those who contribute to the documentation. Special thanks to
Michael (Monty) Widenius, MySQL’s lead developer. He has not only created a ter-
rific relational database, but has offered his advice and expertise to the authors of
this book.
xi