. 1
( 132 .)



>>

®
MySQL /PHP
Database
Applications,
Second Edition
Brad Bulger, Jay Greenspan,
and David Wall
®
MySQL /PHP Database Applications,
Second Edition
®
MySQL /PHP
Database
Applications,
Second Edition
Brad Bulger, Jay Greenspan,
and David Wall
MySQL®/PHP Database Applications, Second Edition
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com

Copyright © 2004 by Wiley Publishing, Inc., Indianapolis, Indiana
ISBN: 0-7645-4963-4
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
2O/RW/RQ/QT
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by
any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under
Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of
the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance
Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher
for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd.,
Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, E-Mail: permcoordinator@wiley.com.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHOR HAVE
USED THEIR BEST EFFORTS IN PREPARING THIS BOOK, THEY 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. NO WARRANTY MAY BE CREATED OR EXTENDED BY
SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE ADVICE AND STRATEGIES
CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH
A PROFESSIONAL WHERE APPROPRIATE. 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.

For general information on our other products and services or to obtain technical support, please contact our
Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317)
572-4002.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not
be available in electronic books.
Library of Congress Cataloging-in-Publication Data: 2002114859
Trademarks: Wiley, the Wiley Publishing logo, and related trade dress are trademarks or registered trademarks
of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used
without written permission. MySQL is a registered trademark of MySQL AB Company. All other trademarks
are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor
mentioned in this book.




is a trademark of Wiley Publishing, Inc.
About the Authors
Brad Bulger can remember when computers were as big as refrigerators and old-
timers would come into the machine room and call them “mini.” After working for
several companies that no longer exist, he is now a member of The Madfish Group
(http://www.madfishgroup.com), where he builds Web sites for money. He would
still like to know when the future is going to get here but has a sneaking suspicion
he already knows.
Jay Greenspan is a New York“based writer, editor, and technical consultant. He
has contributed to sites run by Apple Computer and Wired Digital, and is author of
MySQL Weekend Crash Course. He runs Trans-City Productions, Inc. (http://www.
trans-city.com), a firm that provides editorial services to high-tech companies.
David Wall is a freelance technical consultant, lecturer, and writer. He specializes
in Linux/Apache/MySQL/PHP (LAMP) servers and in Voice over IP technologies
from IBM and Cisco Systems. His consultancy, David Wall Enterprises (http://
www.davidwall.com), has offices in Washington, D.C., and Sydney.
Credits
PROJECT COORDINATOR
ACQUISITIONS EDITORS
Maridee Ennis
Debra Williams Cauley
Jim Minatel
GRAPHICS AND PRODUCTION
SPECIALISTS
PROJECT EDITORS
Beth Brooks
Kevin Kent
Jennifer Click
Neil Romanosky
LeAndra Hosier
Michael Kruzil
TECHNICAL EDITORS
Zak Greant
PERMISSIONS EDITOR
Bill Patterson
Carmen Krikorian
Liz Warner

MEDIA DEVELOPMENT SPECIALIST
COPY EDITOR
Angela Denny
S. B. Kleinman

PROOFREADING AND INDEXING
EDITORIAL MANAGER
TECHBOOKS Production Services
Mary Beth Wakefield

VICE PRESIDENT & EXECUTIVE
GROUP PUBLISHER
Richard Swadley

VICE PRESIDENT AND
EXECUTIVE PUBLISHER
Bob Ipsen

VICE PRESIDENT AND PUBLISHER
Joseph B. Wikert

EXECUTIVE EDITORIAL DIRECTOR
Mary Bednarek
To Lizma, who™s still here; Jon Postel, who isn™t;
and a free Internet, which might not be much longer

For Catou
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 per-
formed 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 sys-
tem or an e-commerce application, MySQL is a great choice for your data storage.



Is This Book for You?
Quite a few books deal with PHP, and a few 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 the
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 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 five parts.

Part I: Working with MySQL
Before you code any PHP scripts you need to know how to design a database, cre-
ate tables in your database, and get the information you want from the database.
Part I of this book shows you just about everything you need to know to work with
MySQL.

ix
x Preface


Part II: Working with PHP
As an applications developer, you will spend the bulk of your time writing scripts
that access the database and present HTML to a user™s browser. Part II starts by
showing you the basics of the PHP scripting language, covering how PHP works
with variables, conditions, and control structures. Part II also covers many of PHP™s
functions and discusses techniques for writing clean, manageable code.

Part III: Simple Applications
In this part we present two of the nine applications in this book: a guestbook and a
survey. Here you 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 become more complex, as we present applications commonly
used on the Web. You see how you can design a content management system, a
discussion board, a shopping cart, and other useful applications. Along the way
we show you 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
them you can find installation and configuration instructions, quick reference
guides to PHP and MySQL functions, a regular expressions overview, and guides to
MySQL administration. In addition, you can find a few helpful resources, some
snippets of code, and instructions on using the CD-ROM.
Acknowledgments
I owe so many people so many bags of chocolate peanuts for helping me that I
should start a chocolate-peanut farm. Making this book happen, trying to cover
products under very active development, has been like trying to paint an oil por-
trait of a manic chameleon in a camouflage factory. I must single out Debra
Williams Cauley, Acquisitions Editor, and Kevin Kent, Development Editor, for their
help and their patience ” they have been the essence of diplomacy; Jay Greenspan,
for getting me into this; and Liz Warner, for all disclosed and undisclosed forms of
assistance, but especially for helping me stay sane(ish). Thanks so much to MySQL
AB for the generous use of the MySQL Function Reference in Appendix J, and to
Zak Greant, Erik Granstrom, Bill Patterson, and David Sides, CEO of Dolphin, for all
their assistance. To everyone who helped, thank you ” you have our gratitude. ”
Brad Bulger


Thanks to my friends, family, and colleagues for their support and freely shared
expertise during the creation of this book. ” David Wall




xi
Contents at a Glance
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi

Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv

Part I Working with MySQL

Chapter 1 Database Design with MySQL . . . . . . . . . . . . . . . . . 3
Chapter 2 The Structured Query Language for Creating and
Altering Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Chapter 3 The Structured Query Language for Inserting,
Editing, and Selecting Data . . . . . . . . . . . . . . . . . . 53

Part II Working with PHP

Chapter 4 Getting Started with PHP ” Variables . . . . . . . . . . . 91
Chapter 5 Control Structures . . . . . . . . . . . . . . . . . . . . . . . . 117
Chapter 6 PHP™s Built-in Functions . . . . . . . . . . . . . . . . . . . 133
Chapter 7 Writing Organized and Readable Code . . . . . . . . . 191

Part III Simple Applications

Chapter 8 Guestbook 2003, the (Semi-)Bulletproof
Guestbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Chapter 9 Survey . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261

Part IV Not So Simple Applications

Chapter 10 Threaded Discussion . . . . . . . . . . . . . . . . . . . . . . 311
Chapter 11 Content-Management System . . . . . . . . . . . . . . . 349
Chapter 12 Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Chapter 13 Problem-Tracking System . . . . . . . . . . . . . . . . . . 441
Chapter 14 Shopping Cart . . . . . . . . . . . . . . . . . . . . . . . . . . . 477
Chapter 15 XML Parsing . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505
Chapter 16 SOAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519
Chapter 17 Project Management . . . . . . . . . . . . . . . . . . . . . . 537


xii
Part V Appendixes

Appendix A What™s on the CD-ROM . . . . . . . . . . . . . . . . . . . . 557
Appendix B HTML Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
Appendix C Brief Guide to MySQL/PHP Installation and
Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . 571
Appendix D MySQL Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . 583

. 1
( 132 .)



>>