PHP 6 and MySQL 5 for Dynamic Web Sites. Peachpit Press

VISUAL QUICKPRO GUIDE

Larry Ullman

Peachpit Press
1249 Eighth Street
Berkeley, CA 94710
510/524-2178
510/524-2221 (fax)
Find us on the Web at: www.peachpit.com
To report errors, please send a note to: errata@peachpit.com
Peachpit Press is a division of Pearson Education.

PHP 6 and MySQL 5 for Dynamic Web Sites

About This Book

This book teaches how to develop dynamic Web sites with PHP and MySQL, covering
the knowledge that most developers might require. In keeping with the format of the
Visual QuickPro series, the information is discussed using a step-by-step approach with corresponding images. The focus has been kept on real-world, practical examples, avoiding “here’s something you could do but never would” scenarios. As a practicing Web developer myself, I wrote about the information
that I use and avoided those topics immaterial to the task at hand. As a practicing writer, I made certain to include topics and techniques that I know readers are asking about.
The structure of the book is linear, and the intention is that you’ll read it in order. It
begins with three chapters covering the fundamentals of PHP (by the second chapter, you will have already developed your first dynamic Web page). After that, there are three chapters on SQL (Structured Query
Language, which is used to interact with all databases) and MySQL. They teach the basics mof SQL, database design, and the MySQL application in particular. Then there’s one chapter on debugging and error management, information everyone needs. This is followed by a chapter introducing how to use PHP and MySQL together, a remarkably easy thing to do.
The following five chapters teach more application techniques to round out your knowledge. Security, in particular, is repeatedly addressed in those pages. Chapter 14, “Making Universal Sites,” is entirely new to this edition of the book, showing you how to broaden the reach of your sites. Finally, I’ve
included three example chapters, in which the heart of different Web applications are developed, with instructions.

Is this book for you?
This book was written for a wide range of people within the beginner-to-intermediate range. 
The book makes use of XHTML for future compatibility, so solid experience with XHTML, or its forebear HTML, is a must. Although this book covers many things, it does not formally teach HTML or
Web page design. Some CSS is sprinkled about these pages but also not taught.
Second, this book expects that you have one of the following:
◆ The drive and ability to learn without much hand holding, or…
◆ Familiarity with another programming language (even solid JavaScript skills would qualify), or…
◆ A cursory knowledge of PHP Make no mistake: This book covers PHP and MySQL from A to Z, teaching everything you’ll need to know to develop real-world
Web sites, but particularly the early chapters cover PHP at a quick pace. 
For this reason I recommend either some programming experience or a curious and independent spirit when it comes to learning new things. If you find that the material goes too quickly, you should probably start off with the latest edition of my book PHP for the World Wide
Web: Visual QuickStart Guide, which goes at a more tempered pace.
No database experience is required, since
SQL and MySQL are discussed starting at a more basic level.

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 experience

coupled with the value of having written several previous books on the technologies

discussed herein. The focus of this book is on covering the most important knowledge

in the most efficient manner. It will teach you how to begin developing dynamic Web

sites and give you plenty of example code to get you started. All you need to provide is an eagerness to learn. Well, that and a computer.

What Are Dynamic Web Sites?

Dynamic Web sites are flexible and potent creatures, more accurately described as

applications than merely sites. Dynamic Web sites

◆ Respond to different parameters 

(for example, the time of day or the version of the visitor’s Web browser)

◆ Have a “memory,” allowing for user registration and login, e-commerce, and similar processes

◆ Almost always have HTML forms, so that people can perform searches, 

provide feedback, and so forth

◆ Often have interfaces where administrators can manage the site’s content

◆ Are easier to maintain, upgrade, and build upon than statically made sites

There are many technologies available for creating dynamic Web sites. 

The most common are ASP.NET (Active Server Pages, a

Microsoft construct), JSP (Java Server Pages), ColdFusion, Ruby on Rails, and PHP. Dynamic

Web sites don’t always rely on a database, but more and more of them do, particularly

as excellent database applications like MySQL are available at little to no cost.

What is PHP?

PHP originally stood for “Personal Home Page” as it was created in 1994 by Rasmus Lerdorf to track the visitors to his online résumé. As its usefulness and capabilities grew (and as it started being used in more professional situations), it came to mean “PHP: Hypertext Preprocessor.”

According to the official PHP Web site, found at www.php.net (Figure i.1), PHP is a “widely-used general-purpose scripting language that is especially suited for Web development and can be embedded into HTML.” It’s a long but descriptive definition, whose meaning I’ll explain.

Starting at the end of that statement, to say that PHP can be embedded into HTML means

that you can take a standard HTML page, drop in some PHP wherever you need it, and

end up with a dynamic result. This attribute makes PHP very approachable for anyone

that’s done even a little bit of HTML work. Also, PHP is a scripting language, as

opposed to a programming language: PHP was designed to write Web scripts, not standalone

applications (although, with some extra effort, you can now create applications in

PHP). PHP scripts run only after an event occurs—for example, when a user submits a form or goes to a URL. I should add to this definition that PHP is a server-side, cross-platform technology, both

descriptions being important. Server-side refers to the fact that everything PHP does

occurs on the server. A Web server application, like Apache or Microsoft’s IIS (Internet

Information Services), is required and all PHP scripts must be accessed through a

URL (http://-something). Its cross-platform nature means that PHP runs on most operating

systems, including Windows, Unix (and its many variants), and Macintosh.

More important, the PHP scripts written on one server will normally work on another

with little or no modification. At the time the book was written, PHP was

at version 5.2.4, with version 4.4.7 still being maintained. Support for version 4 is being

dropped, though, and it’s recommended that everyone use at least version 5 of PHP. This

edition of this book actually focuses on version 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.

What’s new in PHP 6

Because of the planned extinction of PHP 4, many users and Web hosting companies will

likely make a quick transition from PHP 4 to PHP 5 to PHP 6. To discuss what’s new in

PHP 6, I’ll start with the even bigger differences between PHP 4 and 5.

PHP 5, like PHP 4 before it, is a major new development of this popular programming

language. The most critical changes in PHP 5 involve object-oriented programming

(OOP).Those changes don’t really impact this book, as OOP isn’t covered (I do so in

my book PHP 5 Advanced: Visual QuickPro Guide). With respect to this book, the

biggest change in PHP 5 is the addition of the Improved MySQL Extension, which is

used to communicate with MySQL. The Improved MySQL Extension offers many

benefits over the older MySQL extension and will be used exclusively.

The big change in PHP 6 is support for Unicode, which is to say that PHP can now

handle characters in every language in the world. This is huge, and it’s also one of the

reasons it’s taken a while to release PHP 6. What this means in terms of programming

is covered in Chapter 14, “Making Universal Sites.” The information in that chapter is

also used in Chapter 15, “Example—Message Board.” Beyond Unicode support, PHP 6 cleans

up a lot of garbage that was left in PHP 5 even though the recommendation was not to use

such things. The two biggest removals are the “Magic Quotes” and “register globals” features.

Why use PHP?

Put simply, when it comes to developing dynamic Web sites, PHP is better, faster, and

easier to learn than the alternatives. What you get with PHP is excellent performance,

a tight integration with nearly every database available, stability, portability, and a nearly

limitless feature set due to its extendibility. All of this comes at no cost (PHP is open source) and with a very manageable learning curve. PHP is one of the best marriages I’ve

ever seen between the ease with which beginning programmers can start using it

and the ability for more advanced programmers to do everything they require.

Finally, the proof is in the pudding: PHP has seen an exponential growth in use since its

inception, overtaking ASP as the most popular scripting language being used today. It’s

the most requested module for Apache (the most-used Web server), and by the time this

book hits the shelves, PHP will be on nearly 25 million domains.

Of course, you might assume that I, as the author of a book on PHP (several, actually),

have a biased opinion. Although not nearly to the same extent as PHP, I’ve also developed

sites using Java Server Pages (JSP), Ruby on Rails (RoR), and ASP.NET. Each has

its pluses and minuses, but PHP is the technology I always return to. You might hear

that it doesn’t perform or scale as well as other technologies, but Yahoo! handles over

3.5 billion hits per day using PHP (yes, billion). 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

a complete and up-to-date discussion of all the relevant security concerns is provided

by this book!

How PHP works

As previously stated, PHP is a server-side language. This means that the code you write

in PHP sits on a host computer called a server. The server sends Web pages to the requesting

visitors (you, the client, with your Web browser).

When a visitor goes to a Web site written in PHP, the server reads the PHP code and then

processes it according to its scripted directions. In the example shown in Figure i.2,

the PHP code tells the server to send the appropriate data—HTML code—to the Web

browser, which treats the received code as it would a standard HTML page.

This differs from a static HTML site where, when a request is made, the server merely

sends the HTML data to the Web browser and there is no server-side interpretation

occurring (Figure i.3). Because no serverside action is required, you can run HTML

pages in your Web browser without using a server at all.

To the end user and their Web browser there is no perceptible difference between what

home.html and home.php may look like, but how that page’s content was created will be

significantly different.

What is MySQL?

MySQL (www.mysql.com, Figure i.4) is the world’s most popular open-source database.

In fact, today MySQL is a viable competitor to the pricey goliaths such as Oracle and

Microsoft’s SQL Server. Like PHP, MySQL offers excellent performance, portability, and

reliability, with a moderate learning curve and little to no cost.

MySQL is a database management system (DBMS) for relational databases (therefore,

MySQL is an RDBMS). A database, in the simplest terms, is a collection of interrelated data, be it text, numbers, or binary files, that are stored and kept organized by the DBMS.

There are many types of databases, from the simple flat-file to relational and object-oriented.

A relational database uses multiple tables to store information in its most discernable parts. 

While relational databases may involve

more thought in the design and programming stages, they offer an improvement to

reliability and data integrity that more than makes up for the extra effort required.

Further, relational databases are more searchable and allow for concurrent users.

By incorporating a database into a Web application, some of the data generated by PHP

can be retrieved from MySQL (Figure i.5).

This further moves the site’s content from a static (hard-coded) basis to a flexible one,

flexibility being the key to a dynamic Web site.

MySQL is an open-source application, like PHP, meaning that it is free to use or even

modify (the source code itself is downloadable). There are occasions in which you

should pay for a MySQL license, especially if you are making money from the sales or

incorporation of the MySQL product. 

Check MySQL’s licensing policy for more information on this.

The MySQL software consists of several pieces, including the MySQL server (mysqld,

which runs and manages the databases), the MySQL client (mysql, which gives you an

interface to the server), and numerous utilities for maintenance and other purposes.

PHP has always had good support for MySQL, and that is even more true in the

most recent versions of the language. MySQL has been known to handle databases

as large as 60,000 tables with more than five billion rows. MySQL can work with tables as

large as eight million terabytes on some operating systems, generally a healthy 4 GB

otherwise. MySQL is used by NASA and the United States Census Bureau, among many others.

At the time of this writing, MySQL is on version 5.0.45, with versions 5.1 and 6.0 in development.

The version of MySQL you have affects what features you can use, so it’s important that you know what you’re working with. For this book,MySQL 5.0.45 was used, although you should be able to do everything in this book as long as you’re using a version of MySQL greater than 4.1. (My book MySQL: Visual QuickStart Guide goes into the more

advanced and newer features of MySQL 5 that aren’t used in this book.)

What You’ll Need

To follow the examples in this book, you’ll

need the following tools:

◆ A Web server application (for example, Apache, Abyss, or IIS)

◆ PHP

◆ MySQL

◆ A Web browser (Microsoft’s Internet Explorer, Mozilla’s Firefox, Apple’s Safari, etc.)

◆ A text editor, PHP-capable WYSIWYG application (Adobe’s Dreamweaver qualifies),

or IDE (integrated development environment)

◆ An FTP application, if using a remote server

One of the great things about developing dynamic Web sites with PHP and MySQL is

that all of the requirements can be met at no cost whatsoever, regardless of your operating

system! Apache, PHP, and MySQL are each free; most Web browsers can be had without

cost; and many good text editors are available for nothing.

The appendix discusses the installation process on the Windows and Mac OS X

operating systems. If you have a computer, you are only a couple of downloads away

from being able to create dynamic Web sites (in that case, your computer would represent

both the client and the server in Figures i.2 and i.5). Conversely, you could purchase Web

hosting for only dollars per month that will provide you with a PHP- and MySQL-enabled

environment already online.

Screenshot

Purchase Now !
Just with Paypal

Product details

Price
File Size	12,658 KB
Pages	641 p
File Type	PDF format
ISBN-13  ISBN-10	978-0-321-52599-4  0-321-52599-X
Copyright	2008 by Larry Ullman

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

Basic Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Sending Data to the

Web Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Writing Comments . . . . . . . . . . . . . . . . . . . . . . . . . . 10

What Are Variables? . . . . . . . . . . . . . . . . . . . . . . . . . 14

Introducing Strings . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Concatenating Strings . . . . . . . . . . . . . . . . . . . . . . . 21

Introducing Numbers . . . . . . . . . . . . . . . . . . . . . . . . 23

Introducing Constants . . . . . . . . . . . . . . . . . . . . . . . 27

Single vs. Double Quotation Marks . . . . . . . . . . . . 30

Chapter 2: Programming with PHP

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

Including Multiple Files . . . . . . . . . . . . . . . . . . . . . . 74

Handling HTML Forms, Revisited . . . . . . . . . . . . . 84

Making Sticky Forms . . . . . . . . . . . . . . . . . . . . . . . . 89

Creating Your Own Functions . . . . . . . . . . . . . . . . 92

Chapter 4: Introduction to MySQL

Naming Database Elements . . . . . . . . . . . . . . . . . 108

Choosing Your Column Types . . . . . . . . . . . . . . . 110

Choosing Other Column Properties . . . . . . . . . . 114

Accessing MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 116

Chapter 5: Introduction to SQL

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 SQL and MySQL

Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . 158

Performing Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . 173

Grouping Selected Results . . . . . . . . . . . . . . . . . . . 178

Creating Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

Using Different Table Types . . . . . . . . . . . . . . . . . 185

Performing FULLTEXT Searches . . . . . . . . . . . . 188

Performing Transactions . . . . . . . . . . . . . . . . . . . . 194

Chapter 7: Error Handling and Debugging

Error Types and Basic Debugging . . . . . . . . . . . . 200

Displaying PHP Errors . . . . . . . . . . . . . . . . . . . . . . 206

Adjusting Error Reporting in PHP . . . . . . . . . . . . 208

Creating Custom Error Handlers . . . . . . . . . . . . . 211

PHP Debugging Techniques . . . . . . . . . . . . . . . . . 216

SQL and MySQL Debugging Techniques . . . . . . 220

Chapter 8: Using PHP with MySQL

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 with PHP . . . . . . . . . . . . . . . . . 251

Chapter 9: Common Programming Techniques

Sending Values to a Script . . . . . . . . . . . . . . . . . . . 260

Using Hidden Form Inputs . . . . . . . . . . . . . . . . . . 264

Editing Existing Records . . . . . . . . . . . . . . . . . . . . 270

Paginating Query Results . . . . . . . . . . . . . . . . . . . . 277

Making Sortable Displays . . . . . . . . . . . . . . . . . . . 285

Chapter 10: Web Application Development

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

Making a Login Page . . . . . . . . . . . . . . . . . . . . . . . 328

Making the Login Functions . . . . . . . . . . . . . . . . 331

Using Cookies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336

Using Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349

Improving Session Security . . . . . . . . . . . . . . . . . . 358

Chapter 12: Security Methods

Preventing Spam . . . . . . . . . . . . . . . . . . . . . . . . . . . 362

Validating Data by Type . . . . . . . . . . . . . . . . . . . . . 369

Preventing XSS Attacks . . . . . . . . . . . . . . . . . . . . . 374

Preventing SQL Injection Attacks . . . . . . . . . . . . 377

Database Encryption . . . . . . . . . . . . . . . . . . . . . . . 383

Chapter 13: Perl-Compatible Regular Expressions

Creating a Test Script . . . . . . . . . . . . . . . . . . . . . . . 390

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

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

Chapter 15: Example—Message Board

Making the Database . . . . . . . . . . . . . . . . . . . . . . . 442

Writing the Templates . . . . . . . . . . . . . . . . . . . . . . 451

Creating the Index Page . . . . . . . . . . . . . . . . . . . . . 460

Creating the Forum Page . . . . . . . . . . . . . . . . . . . . 461

Creating the Thread Page . . . . . . . . . . . . . . . . . . . 466

Posting Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . 471

Chapter 16: Example—User Registration

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

Chapter 17: Example—E-Commerce

Creating the Database . . . . . . . . . . . . . . . . . . . . . . 530

The Administrative Side . . . . . . . . . . . . . . . . . . . . 536

Creating the Public Template . . . . . . . . . . . . . . . . 553

The Product Catalog . . . . . . . . . . . . . . . . . . . . . . . . 557

The Shopping Cart . . . . . . . . . . . . . . . . . . . . . . . . . 569

Recording the Orders . . . . . . . . . . . . . . . . . . . . . . . 579

Appendix A: Installation 

Installation on Windows . . . . . . . . . . . . . . . . . . . . 588

Installation on Mac OS X . . . . . . . . . . . . . . . . . . . 591

MySQL Permissions . . . . . . . . . . . . . . . . . . . . . . . . 594

Testing Your Installation . . . . . . . . . . . . . . . . . . . . 598

Configuring PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . 601

Index 603

  ●▬▬▬▬▬❂❂❂▬▬▬▬▬●

●▬▬❂❂▬▬●

●▬❂▬●

●❂●

═════● ●═════