Practical PHP and MySQL

Practical PHP

and MySQL®

NEGUS LIVE LINUX SERIES

Your practical, hands-on guides to getting

real results with free software

Books in the Negus Live Linux Series encourage and challenge

you to advance in the free software world. Boot the live DVD or

CD that comes with each book and watch the Linux system,

applications, and content described in the book come to life

before your eyes.

Start as a novice by trying out examples and finish as a profes￾sional, mastering the many topics covered in the series, from

building PHP/MySQL sites to customizing live CDs and more.

When you are finished, you will know how to use, customize, and

rebuild that free and open source software yourself.

Overseeing the series is Christopher Negus, bestselling author

of the Red Hat Linux Bible series, Linux Toys series, and the

signature book for this series, Live Linux CDs.

Practical PHP

and MySQL®

Building Eight Dynamic

Web Applications

Jono Bacon

NEGUS LIVE LINUX SERIES

Upper Saddle River, NJ ■ Boston ■ Indianapolis ■ San Francisco

New York ■ Toronto ■ Montreal ■ London ■ Munich ■ Paris ■ Madrid

Cape Town ■ Sydney ■ Tokyo ■ Singapore ■ Mexico City

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 the publisher was aware of a trade￾mark claim, the designations have been printed with initial capital letters or in all capitals.

The author and publisher have taken care in the preparation of this book, but make no expressed or

implied warranty of any kind and assume no responsibility for errors or omissions. No liability is

assumed for incidental or consequential damages in connection with or arising out of the use of the

information or programs contained herein.

The publisher offers excellent discounts on this book when ordered in quantity for bulk purchases

or special sales, which may include electronic versions and/or custom covers and content particular

to your business, training goals, marketing focus, and branding interests. For more information,

please contact:

U.S. Corporate and Government Sales

(800) 382-3419

[email protected]

For sales outside the United States, please contact:

International Sales

[email protected]

Visit us on the Web: www.prenhallprofessional.com

Library of Congress Cataloging-in-Publication Data

Bacon, Jono.

Practical PHP and MySQL : building eight dynamic web applications / Jono Bacon.

p. cm.

Includes index.

ISBN 0-13-223997-3 (pbk. : alk. paper) 1. PHP (Computer program language) 2. MySQL

(Electronic resource) 3. Web site development. I. Title.

TK5105.888.B325 2007

005.13’3—dc22

2006027701

Copyright © 2007 Pearson Education, Inc.

All rights reserved. Printed in the United States of America. This publication is protected by

copyright, and permission must be obtained from the publisher prior to any prohibited reproduction,

storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical,

photocopying, recording, or likewise. For information regarding permissions, write to:

Pearson Education, Inc.

Rights and Contracts Department

75 Arlington Street, Suite 300

Boston, MA 02116

Fax: (617) 848-7047

ISBN 0-13-223997-3

Text printed in the United States on recycled paper at R. R. Donnelley in Crawfordsville, Indiana.

First printing: November 2006

To my family for their never-ending support…

This page intentionally left blank

vii

Contents

Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi

About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii

Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

A Different Approach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

What You Need to Use This Book. . . . . . . . . . . . . . . . . . . . . . . . . . . 3

Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

Onward. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

CHAPTER 1 A New Approach. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

The Technology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

How the Dynamic Web Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

CHAPTER 2 Getting Started with PHP and MySQL . . . . . . . . . . . . . 15

Setting Up PHP and MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Getting Started with PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Rolling in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Connecting to MySQL in PHP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

CHAPTER 3 Running the Projects. . . . . . . . . . . . . . . . . . . . . . . . . . . 53

About the Disc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

Running the Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Using XAMPP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

CHAPTER 4 Building a Weblog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Project Overview: Blogtastic Use Case . . . . . . . . . . . . . . . . . . . . . . 64

Building the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Starting to Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Viewing Specific Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

Building the Category Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92

Don’t Just Let Anyone Log In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95

Rolling Your Own Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Creating New Blog Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Update a Blog Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110

CHAPTER 5 Discussion Forums . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

Under the Hood . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

Building Your Own Forums. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

Creating the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

Creating the Site Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

Displaying the Forums . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124

Managing User Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133

Posts and Replies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

Creating Administrator-Specific Pages . . . . . . . . . . . . . . . . . . . . . 158

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167

CHAPTER 6 Creating a Shopping Cart . . . . . . . . . . . . . . . . . . . . . . 169

Project Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170

Building the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

Starting to Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

Managing User Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

Displaying and Selecting Products . . . . . . . . . . . . . . . . . . . . . . . . 184

Checking It Out . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196

Administrator Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216

viii Contents

CHAPTER 7 Building an Online Auction Site. . . . . . . . . . . . . . . . . . 219

Project Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219

Building the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220

Starting to Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

Displaying Auction Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226

Dealing with Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240

Adding an Item . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243

Processing Auctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259

Scheduling the Page to Be Run . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263

CHAPTER 8 Creating a Web-Based Calendar . . . . . . . . . . . . . . . . . 265

Project Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265

Building the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266

Starting to Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267

Viewing Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

CHAPTER 9 FAQ Content Management System . . . . . . . . . . . . . . . 303

Project Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304

Building the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305

Starting to Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310

Displaying Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321

Dealing with Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330

Adding and Moderating Questions . . . . . . . . . . . . . . . . . . . . . . . 341

Managing Subjects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356

Managing Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360

Subject Ownership . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

CHAPTER 10 Building a Re-Usable Project . . . . . . . . . . . . . . . . . . . . 379

Project Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380

Building the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

Using Directories Intelligently. . . . . . . . . . . . . . . . . . . . . . . . . . . . 385

Starting to Code: Building the Backbone . . . . . . . . . . . . . . . . . . . 386

Downloading Releases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393

Viewing Screenshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396

Available Projects Viewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397

Contents ix

Administering Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398

Changing General Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406

Managing Downloads. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407

Deleting Releases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414

Managing Screenshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415

Deleting Images . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419

Adding a New Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420

Deploying the Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424

CHAPTER 11 Building a News Web Site . . . . . . . . . . . . . . . . . . . . . . 425

Project Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425

Installing PEAR Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426

Building the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428

Starting to Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431

Handling User Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440

Viewing and Rating Stories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445

Managing Stories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451

Managing Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457

Creating Your Search Engine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 467

APPENDIX A Web Site Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

Project Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

Laying Out the Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471

Starting to Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474

Start Building the Stylesheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479

Formatting the Main <div> Items . . . . . . . . . . . . . . . . . . . . . . . . . 481

Creating an About Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487

Creating a Frequently Asked Questions Page . . . . . . . . . . . . . . . . 489

Formatting Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499

x Contents

xi

Foreword

Listen to podcasts by Jono Bacon and friends at LUG Radio (www.lugradio.org) and

you get a sense of both the spirit and thoughtfulness Jono brings to the open source

community. At one moment they speak seriously about hurdles in contributing code

to free software projects, while the next they offer a Monty Python-like discussion

on which Linux distribution each of them most resembles.

Practical PHP and MySQL reflects Jono’s commitment to the spirit of making

open source subjects accessible to everyone. The book carefully walks you through

the code for eight useful, dynamic Web applications. Projects are presented in a

playful way, like the forum project that touts horror movies that make you “hide

behind the couch.” And everything in the book can be run live, modified, saved,

and reused from the included live CD.

Although the tools you need to create Web content are readily available from

the open source community, having a skillful guide like Jono Bacon to help you

create dynamic Web applications from those tools is a real treat. The results form a

foundation for developing your own blogs, forums, shopping carts, and other Web

destinations that should significantly cut your startup time.

I am thrilled to have Jono Bacon’s Practical PHP and MySQL as one of the first

books in the Negus Live Linux Series. Its content perfectly suits the goals of

the series to put quality free and open source software covering various topics into

peoples’ hands so they can quickly get high-quality results. I hope you enjoy learn￾ing from this book as much as I have.

—Christopher Negus

Series Editor, Negus Live Linux Series

xii

About the Author

Jono Bacon works for Canonical as the Ubuntu community manager and is an

established speaker, author, and regular contributor to the Open Source community.

As an author, Bacon co-authored Linux Desktop Hacks and the Official Ubuntu

Book, and has written more than 400 published articles in more than 14 publica￾tions. Bacon has also contributed as a columnist for Linux Format, Linux User &

Developer, and PC Plus, and is an O’Reilly Network weblog author.

In addition, Bacon is a regular contributor to Open Source in a range of pro￾jects, a lead developer on the Jokosher (www.jokosher.org) project, and one of the

co-founders of the popular LUGRadio (www.lugradio.org) podcast—a show with

more than 15,000 listeners and an annual event that pulls visitors from around the

world.

xiii

Acknowledgments

Writing thank-you lists is always hard, because I always end up leaving out some￾one important. I want to give thanks to the following people for their incredible sup￾port and help:

Susan Curtis, John and Pauline Bacon, Simon and Martin Bacon,

Banger and Frankie, Prentice Hall (Debra Williams-Cauley, Songlin Qiu),

LUGRadio (Stuart Langridge, Matthew Revell, Adrian Bradshaw),

OpenAdvantage (Paul Cooper, Elliot Smith, Scott Thompson), #php,

#mysql, and #lugradio on Freenode, the LUGRadio community,

Kai “Oswald” Seidler, and many more.

This page intentionally left blank