Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Copyright
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 trademark claim, the designations have
been printed with initial capital letters or in all capitals.
The authors 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
Quigley, Ellie.
PHP and MySQL by example / Ellie Quigley with Marko Gargenta.
p. cm.
Includes index.
ISBN 0-13-187508-6 (pbk. : alk. paper) 1. Web site development. 2.
Web databases—Design. 3. PHP (Computer program language) 4. MySQL
(Electronic resource) I. Gargenta, Marko. II. Title.
TK5105.888.Q54 2006
006.7'6—dc22
2006030160
Copyright © 2007 Pearson Education, Inc.
All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission

publish our book, the learning curve was steep, and after the initial draft was done, I began teaching “An Introduction to
PHP and MySQL Programming” from the PDF version of that first draft. I noticed that more Web designers were
signing up than programmers, and they came in with trepidation that it would be way over their heads. But with the
real-world examples and labs we provided, they started to enjoy feelings of success on the first morning. It was
wonderful to witness both designers and programmers sharing their experiences without the artificial boundary that has
kept them isolated from each other in the workplace.
The mission of PHP and MySQL by Example is to create a gentle yet thorough introduction to the shared power of PHP
and MySQL, to make static HTML pages dynamic. The labs and exercises have been tested by myself, Marko, and our
students. I think you will find this “by Example” book a helpful and complete guide, no matter what side of the Web
site you support, or even if you are just starting your own.
Acknowledgments
Many people helped with the creation of this book. I’d like to thank Mark L. Taub, my longtime editor at Prentice Hall;
Vanessa Moore, the most gifted compositor on the planet; and Julie Nahil, a great production editor. Matthew
Leingang, Sander van Zoest, David Mercer, and Jason Wertz provided extremely helpful manuscript reviews. Any
remaining mistakes are my own.
I’d also like to thank the students in my classes who provided valuable input for the labs. These include Rita McCue,
Sanjay Shahri, Ryan Belcher, Debra Anderson, and Catherine Nguyen.
The fantastic illustrations in the book were created by Elizabeth Staechelin and Daniel Staechelin. And many thanks to
the artists who provided artwork for the art gallery example. They are Elliott Easterling, Laura Blair, Stuart Sheldon,
and Todd Brown.
Errata and solutions to the labs can be found on the book’s Web site at
www.prenhallprofessional.com/title/0131875086. The Northwind database script, used in the chapters, can be found at
http://marakana.com/download/sql/northwind.sql.
Ellie Quigley
San Francisco, California
September 2006
!
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 1. Introduction
1.1. From Static to Dynamic Web Sites

site’s information, and a scripting language that can retrieve the information from the database. Google and Yahoo! are
examples of dynamic sites, search engines that create customized pages based on a key word or phrase you type. The
resulting page is created on the fly, customized just for you, based on your request. Farms of powerful computers all
over the world are constantly taking such requests and processing them. In the early days of the Web, processing was
done through the Common Gateway Interface, called CGI, a server-side technology that allowed Web developers to
create dynamic sites. Most CGI scripts were written in Perl. A browser would send information from an HTML Web
page, such as information from a fillout form, to the server for processing. The server then would create a gateway to an
external program called a CGI script or helper program. Although any programming language could be used, the most
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
popular language for CGI was Perl. The Perl script would then parse the data, generate HTML based on certain
conditions, send an e-mail, open a file or database, and send information through the gateway back to the server, where
it then was relayed onto the browser. (See the top portion of Figure 1.1.)
Figure 1.1. The process of creating dynamic Web sites.
Although the basic underlying process of creating dynamic Web sites hasn’t changed, new languages have evolved,
making the process much simpler by allowing the processing to be embedded right in the server.
[1]
PHP is such a
language. A PHP script can be embedded right in the Web page. It can generate HTML and images on the fly, retrieve
up-to-date information from a file or database, encrypt data, remember user preferences, and so on. It executes PHP
instructions and inserts the results right back into the Web page before the server sends the page back to the browser,
thus making the page truly dynamic. (See the bottom portion of Figure 1.1.)
[1]
To imply that Perl is outdated is not the intention here. Perl has Mason and mod_perl to allow Perl and HTML to be
embedded in the Apache server.
Web sites often handle huge amounts of information. A database management system is essential for storing, retrieving,
and updating that information. MySQL, the world’s most popular open source database, has become the choice for
applications that interact with database-enabled Web sites. PHP and MySQL, working together, form a marriage of two

Lerdorf in the C language and open-sourced; that is, made freely available. PHP was very Perl-like in sytnax, but
whereas Perl is an all-purpose, jack-of-all-trades scripting language, PHP was designed specifically to master the Web.
PHP instructions can be embedded with HTML right in the Web page so that whenever the page is loaded, PHP can
execute its code. PHP made processing forms easier by providing automatic interpretation of form variables. It allowed
for interaction with databases. It enabled users to create simple dynamic Web sites. The toolset Rasmus Lerdorf
developed was so popular that in 1997, PHP/FI 2.0 was released. Due to the popularity of this new release, Lerdorf was
soon joined by a core group of developers, who continued to provide improvements and enhancements to the new
language. By this time, there where thousands of users and approximately 50,000 Web sites running PHP/FI pages.
Zeev Suraski and Andi Gutmans, two students attending Technion-Israel Institute of Technology, needed a language for
their university e-commerce project. They chose PHP/FI for their project. Dissatisfied with its limitations and bugs,
they put their project aside, and rewrote PHP almost from scratch. PHP 3.0 was a significant departure from the
previous code base. The new language supported add-on modules and had a much more consistent syntax. At this time,
the meaning of the acronym changed as well. PHP now stands for PHP: Hypertext Preprocessor. PHP 3.0 was released
in 1998 and is the closest version to PHP today.
By May 2000, PHP 4 was released. The core of PHP 4 was entirely rewritten to improve the performance of complex
Web applications and improve modularity of the platform. Zeev Suraski and Andi Gutmans, the authors of PHP 3,
introduced a new parsing engine, called the Zend engine,
[3]
which is the scripting language that powers PHP today.
Because of their internationally recognized authority, Suraski and Gutmans founded Zend Technologies, the PHP
company, and their contributions to PHP have been a major reason for its explosive worldwide growth. See
www.zend.com.
[3]
The term Zend is a portmanteau, a word created by combining the letters in their first names: Zeev and Andrew
Version 4 offered an open Application Programming Interface (API), allowing other programmers to write modules for
PHP, modules that would extend its functionality, modules that allowed PHP 4 to support most of the available
databases and Web servers available. With this release, PHP became a serious programming language and platform for
developing and deploying complex Web applications.
The latest incarnation of PHP was released in July 2004. PHP 5 added a whole new object-oriented model to the
language. The new model is based on Zend Engine 2 and greatly improves PHP performance and capabilities. Most of

Figure 1.3. PHP download page.
!

1.3. About MySQL
Monty Widenius

Today many organizations face the double threat of increasing volumes of data and transactions coinciding with a need
to reduce spending. Many such organizations are migrating to open source database management systems to keep costs
down and minimize change to their existing systems. The world’s most popular of these open source database systems
(it’s free to download, use, and modify) is MySQL. It is distributed and supported by MySQL AB, a Swedish
commercial company founded by the original developers, David Axmark and Michael “Monty” Widenius, who wrote
MySQL in 1995. MySQL has its roots in mSQL or mini SQL, a lightweight database developed at Bond University in
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Australia, to provide fast access to stored data with low memory requirements. Its symbol is a dolphin called “Sakila”
representing “speed, power, precision and good nature of the MySQL database and community.”
[4]

[4]
Monty Widenius, MySQL founder and CT0, from a news release: http://www.mysql.com/news-and-
events/news/article_116.html.
1.3.1. Where to Get MySQL and Documentation
MySQL is installed on more than 6 million servers worldwide to power many high-volume and business-critical Web
sites. See http://www.mysql.com/company/factsheet.html. MySQL was created by MySQL AB and is available for
download from their Web site at http://www.mysql.com/, where you can also find the latest information about MySQL
software and MySQL AB (see Figures 1.4 and 1.5).
Figure 1.4. The MySQL home page.
!
!
databases on the Internet.
MySQL serves as a back end for all kinds of information such as e-mail, Web images and content, games, log files, and
so on. The server can be embedded in applications such as cell phones, electronic devices, public kiosks, and more.
1.3.3. How to Install MySQL and PHP
Appendix E of this book contains instructions on the installation procedures for Windows, UNIX, Macintosh, and so
on. The source code for PHP and MySQL can also be found on the CD included in the back cover of this book.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.3.4. Advantages of MySQL and PHP
Certain technologies play together better than others. PHP, a simple and powerful scripting language, and MySQL, a
solid and reliable database server, make a perfect marriage between two modern technologies for building database-
driven, dynamic Web sites. Some of the advantages of both PHP and MySQL are:
• High performance
• Built-in libraries
• Extensibility
• Relatively low cost
• Portability
• Developer community
• Ease of learning
High Performance
PHP is no longer considered just a grassroots scripting language, but now with PHP 5, and its highly efficient built-in
Zend engine, PHP accommodates developers and IT decision makers in the business trend to rapidly release and update
software on the Web faster than conventional programming cycles have allowed.
MySQL, a highly optimized database server, provides the response time and throughput to meet the most demanding
applications.
With PHP scripts connected to a MySQL database, millions of pages can be served on a single inexpensive server.
Built-In Libraries
PHP comes with many built-in functions addressing common Web development tasks. Problems encountered by other
programmers have been solved and packaged into a library of routines, made available to the PHP community. The
official PHP Web site at http://www.php.net provides excellent documentation explaining how to use all of the
functions currently available.

2. What!is!the!meaning!of!open!source!software?!
3. Why!was!PHP!developed,!what!it!is!used!for,!and!where!can!you!get!it?!
4. What!is!MySQL!used!for!and!where!can!you!get!it?!
5. What!are!the!benefits!of!using!PHP!and!MySQL?!
6. Why!do!PHP!and!MySQL!work!well!together?!
1.4.2. What’s Next?
In Chapter 2, “Getting Started,” we will review the life cycle of a typical Web page that uses PHP. We will learn how to
create and execute simple PHP scripts both from the browser and at the command line. We will talk about built-in
functions and how to use them by viewing the PHP documentation Web site.
!
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 2. Getting Started

2.1. The Life Cycle of a Web Page
Before you start learning PHP, it is helpful to understand what makes up a dynamic Web page and how PHP interacts
with the other applications involved in the process. Figure 2.1. diagrams the life cycle of a typical Web page.
Figure 2.1. The life cycle of a typical Web page.

!
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
2.1.1. Analysis of a Web Page
The Players
The players in Figure 2.1 represent the applications involved in the life cycle of a Web page. When you start using PHP,
it is normally not the only player, but part of a team of players, including a browser (Firefox, Netscape, Internet
Explorer), a network (HTTP), a server (Apache, Windows IIS, Sambar), a server module (PHP, ASP, ColdFusion), and
external files or a database (MySQL, Oracle, Sybase).
The Steps
Figure 2.1 illustrates the life cycle of a Web page from when the client makes a request until it gets a response. We will
explain each of steps by the number shown in the diagram.
1.# "#!$%&!'&($!)*+&!,(!$%&!+*-./-01!2&!)&&!$%&!3'*&#$1!,/!4/,2)&/!2%&/&!$%&!/&56&)$!*)!0-+&7!

$%&!6)&/B)!4/,2)&/!3,#)*)$)!,(!W6)$!='-*#!I8KE!-#+!$&<$7
5.# :(!$%&!A&4!=-.&!3,#)*)$)!,(!JIJ!2*$%!K9QXE!R,/!-#9!,$%&/!+-$-4-)&S!)$-$&0&#$)1!$%&#!
JIJ!0-9!0-@&!(6/$%&/!/&56&)$)!$,!$%&!+-$-4-)&!$,!/&$/*&H&1!)&#+1!,/!6=+-$&!*#(,/0-$*,#!
,#!$%&!('97
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
2.2. The Anatomy of a PHP Script
A PHP script is a file (ending with a .php extension) consisting of text, HTML, and PHP instructions interspersed
throughout the file. The PHP instructions are contained within two HTML style tags; <?php is the opening tag and ?>
is the closing tag. Everything between these two tags is interpreted by the PHP module (also called interpreter) and
converted to regular text and HTML before being sent back to the requesting browser. If, for example, one of the PHP
instructions is to get today’s date from the server, PHP will get the date and replace the PHP instruction with the current
date. When the browser gets the file, it will not see the PHP tags or any of the PHP instructions; it will get only what
PHP generated as a result of its processing. Consider the following simple PHP instruction consisting of an echo
statement containing the string "Hello, world.<br />", some plain text, and an HTML break tag.
What the PHP interpreter gets:
<?php echo "Hello, world.<br />"; ?>
!
What the Web browser gets:
Hello, world.<br />
!
2.2.1. The Steps of Writing a PHP Script
After you have installed PHP successfully (see Appendix E for installation instructions), and the Web server is running,
it is time to write your first PHP script.
Finding a Text Editor
Because PHP is a scripting language designed to be integrated with other text documents, most commonly HTML, you
will write your scripts in a text editor. Some popular text editors are BBEdit (Macintosh), Wordpad, Notepad
(Windows), pico, vi, emacs (Linux/UNIX), and so on. Also available are third-party editors, TextPad and WinEdit, as
well as integrated development environments (IDEs) such as Dreamweaver and Eclipse.
Naming the PHP File—The .php Extension
When you name the file, be sure to add the PHP extension to its name. Normally the extension is .php, but this

Z 8%*)!*)!$%&!3',)*#.!JIJ!$-.7!:$!$&'')!JIJ!$,!)$,=!=/,3&))*#.7

Additional PHP Tags
To promote flexibility, PHP supports three other types of tags, but the full PHP tags just described are really the most
reliable, and your particular PHP configuration may not support the ones listed in Table 2.1.
Table 2.1. Additional PHP Tags
Tag Description
JIJ!$-.)
<?php
!
php code
!
?>

I8KE!)$9'&!$-.)
<script language="php">
!
php code
!
</script>

PQJT)$9'&
<%
!
php code
!
%>

JIJ!)%,/$!$-.)
<?

The Script Content
Developers often prefer to seperate the presentation code (HTML, XML) from business logic (program instructions)
and PHP offers the best of both worlds. A PHP script can be an independent file consisting of PHP tags and
instructions, or it can be embedded in an HTML document. The PHP tags are often inserted between the <body> tags
of an HTML document, although they can be inserted anywhere on the page. To format your output, HTML tags can be
embedded in PHP statements as well, but cannot stand alone in a PHP script. You can have multiple PHP scripts within
the HTML page. Figure 2.2 demonstrates how PHP blocks of instructions are inserted in a Web page. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Figure 2.2. PHP and the Web page.

!
Before sending the page to the browser, the Web server will send any PHP instructions to the PHP module for
preprocessing. The PHP module starts interpreting code when it finds the first <?php opening tag, and continues until
it reaches the closing tag ?>, executing the code between the tags. Any other text in the file is left as is. If there is
output, this output replaces the original PHP code between the tags. PHP tags will be removed. HTML tags will be left
alone. The Web server will send the resulting page, consisting of plain HTML and text, back to the browser that
requested the page. (To see the source code received by the browser, go to the browser’s “View” menu option and
select “Page Source” or “Source”.)
Example 2.2.
(Filename: first.php)

<html>
<head>
<title>Hello World</title>

&-3%!JIJ!)$-$&0&#$7
Z 8%&!
?>
!$-.!*)!$%&!&#+*#.!$-.7!Q&&!>*.6/&![7Z7
!
Figure 2.3. Output of the PHP script in the browser.
!

Quoting in Strings
Many statements you write in PHP will contain text, called strings, such as "Have a good day!". We have
devoted a whole chapter to strings (Chapter 6, “Strings”), but as you start learning PHP, you should be aware of some
basic rules concerning strings to write even the simplest PHP statements.
1. All strings must be enclosed in a pair of either single or double quotes. The quotes must be matched:
"Hello there" or 'Hello there'
2. To join two strings together, use the concatenation operator, a dot:
"Hello, " . "world"
3. If you need a quote to be printed literally, precede it with a backslash or enclose a single quote within double
quotes:
"\"Ouch\"" or "I don't care"
Printing Strings
You can start printing output with the echo and print language constructs. Print displays a string. Parentheses are
not required around the string. To print more than one string with print, you can use the dot to concatenate the strings
into one string. Echo is like print but allows you to print more than one string by separating each string with a
comma. These constructs are quite simple and do not do any fancy formatting (see Example 2.3). If you need to format
the output, PHP provides a number of functions, including the printf(), sprintf(), and fprintf() functions
described in Chapter 6, “Strings.”
Example 2.3.
<?php

echo "Hello, ", "world!", "<br />"; // Don't use

PHP doesn’t understand the HTML opening < tag.
!
!
Figure 2.6. Output of PHP code after HTML <font line was moved outside the PHP script.

!
Example 2.4.
<html><head><title>Mixing HTML and PHP</title>
<body>

1 <?php
2 <font size="+2"> // Wrong!!! Cannot place HTML directly
// into PHP script
3 print "It's such a perfect day!<br />"; // Okay here
within
// the statement
?>

</body>
Explanation
Y 8%&!JIJ!)3/*=$!)$-/$)!%&/&7
[ 8%*)!I8KE!
<font>
!$-.!*)!='-3&+!2*$%*#!$%&!JIJ!)3/*=$7!8%*)!#,$!-!'&.-'!JIJ!)$-$&0&#$7!
],6!3,6'+!&04&+!$%&!
<font>
!$-.!2*$%*#!-!JIJ!
print
!)$-$&0&#$!-)^!!
print <font size='+2'>;