O'Reilly logo

Learning PHP and MySQL by Jon A. Phillips, Michele E. Davis

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 1. Dynamic Content and the Web

There are two types of web pages: static and dynamic. A static site provides hyperlinked text and perhaps a login screen, but beyond that, it doesn’t require additional participation from the user. http://www.startribune.com is an example of a static site, except that you do have to register to view articles. http://www.amazon.com is an example of a dynamic web site, because your ordering data is logged, and Amazon offers recommendations based on your purchasing history when you access their page. In other words, dynamic means that the user interacts more with the web site, beyond just reading pages, and the web site responds accordingly.

Creating dynamic web pages—even a few years ago—meant writing a lot of code in the C or Perl languages, and then calling and executing those programs through a process called a Common Gateway Interface (CGI). Having to create executable files doesn’t sound like much fun, and neither does learning a whole new complicated language. Well, thankfully, PHP and MySQL make creating dynamic web sites simpler, easier, and faster.

PHP and MySQL’s Place in Web Development

PHP is a programming language designed to generate web pages interactively on the computer serving them, called a web server . Unlike HTML, where the web browser uses tags and markup to generate a page, PHP code runs between the requested page and the web server, adding to and changing the basic HTML output. For example, PHP code could be used to display a counter of visitors to a site.

PHP, in less than 20 lines of code, can store the IP address from which a page request comes in a separate file, and then display the number of unique IP addresses that visited a particular site. The person requesting the web page doesn’t know that PHP generated the page, because the counter text is part of the standard HTML markup language that the PHP code generated.

PHP makes web development easy, because all the code you need is contained within the PHP framework. This means that there’s no reason for you to reinvent the wheel each time you sit down to develop a PHP program; that would be something you’d have to do if you were using a compiled language like C.

While PHP is great for developing web functionality, it is not a database. The database of choice for PHP developers is MySQL, which acts like a filing clerk for PHP-processed user information. MySQL automates the most common tasks related to storing and retrieving specific user information based on your supplied criteria.


Take our Amazon example; the recommendations Amazon offers you can be stored in a MySQL database, along with your prior order information.

MySQL is easily accessed from PHP, and they’re commonly used together as they work well hand in hand. An added benefit is that PHP and MySQL run on various computer types and operating systems, including Mac OS X, Windows-based PCs, and Linux.

Advantages of Using PHP with MySQL

There are several factors that make using PHP and MySQL together a natural choice:

PHP and MySQL work well together

PHP and MySQL have been developed with each other in mind, so they are easy to use together. The programming interfaces between them are logically paired up. Working together wasn’t an afterthought when the developers created the PHP and MySQL interfaces.

PHP and MySQL have open source power

As they are both open source projects, PHP and MySQL can both be used for free. MySQL client libraries are no longer bundled with PHP. Advanced users have the ability to make changes to the source code, and therefore, change the way the language and programs work.

PHP and MySQL have community support

There are active communities on the Web in which you can participate and they’ll answer your questions. You can also purchase professional support for MySQL if you need it.

PHP and MySQL are fast

Their simplicity and efficient design enables faster processing.

PHP and MySQL don’t bog you down with unnecessary details.

You don’t need to know all of the low-level details of how the PHP language interfaces with the MySQL database, as there is a standard interface for calling MySQL procedures from PHP. Online APIs at http://www.php.net offer an unlimited resource.

The Value of Open Source

As we mentioned above, both PHP and MySQL are open source projects, so there’s no need to worry about user licenses for every computer in your office or home. In open source projects and technologies, programmers have access to the source code; this enables individual or group analysis to identify potentially problematic code, test, debug, and offer changes as well as additions to that code. For example, Unix—the forerunner in the open source software community—was freely shared with university software researchers. Linux, the free alternative to Unix, is a direct result of their efforts and the open source licensing paradigm.

As Tim O’Reilly puts it, “Open source licensing began as an attempt to preserve a culture of sharing, and only later led to an expanded awareness of the value of that sharing.” Today, open source programmers share their code changes on the Web via php.net, listservs, and web sites. If you’re caught in a coding nightmare and can’t wake up, the resources mentioned above can and will help you.


We’ll arm you with open source user forums later in this book so you can check them out yourself. We’ll include listservs and web sites so that you have numerous resources if you run into a snafu.

The Components of a PHP Application

In order to process and develop dynamic web pages, you’ll need to use and understand several technologies. There are three main components to creating dynamic web pages: a web server, a server-side programming language, and a database. It’s a good idea to have an understanding of the three basic components for web development using PHP. Start with some rudimentary understanding of the history and purpose of Apache (your web server), PHP (your server-side programming language), and MySQL (your database). This can help you understand how they fit into the web development picture.

Remember that dynamic web pages pull information from several sources simultaneously, including Apache, PHP, MySQL, and Cascading Style Sheets (CSS), which we’ll talk about later.

Birth of PHP

PHP grew out of a need for people to develop and maintain web sites containing dynamic client-server functionality. In 1994, Rasmus Lerdorf created a collection of open source Perl scripts for his personal use, and these eventually were rewritten in C and turned into what PHP is today. By 1998, PHP was released in its third version, turning it into a web development tool that could compete with similar products such as Microsoft’s Active Server Pages (ASP) or Sun’s Java Server Pages (JSP).

The real beauty of PHP is its simplicity coupled with its power, as well as it being an interpreted language, rather than a compiled one.


Compiled languages create a binary .exe file, while interpreted languages work directly with the source code when executing as opposed to creating a standalone file.

PHP is ubiquitous and compatible with all major operating systems. It is also easy to learn, making it an ideal tool for web-programming beginners. Additionally, you get to take advantage of a community’s effort to make web development easier for everyone. The creators of PHP developed an infrastructure that allows experienced C programmers to extend PHP’s abilities. As a result, PHP now integrates with advanced technologies like XML, XSL, and Microsoft’s COM. At this juncture, PHP 5.0 is being used.

Birth of Apache

Apache is a web server that turns browser requests into resulting web pages and knows how to process PHP code. PHP is only a programming language, so without the power of a web server like Apache behind it, there would be no way for web users to reach your pages that contain the PHP language code.

Apache is not the only web server available. Another popular web server is Microsoft’s Internet Information Services (IIS), which is supplied with Windows 2000 and all later versions. For the most part, the differences between Apache and IIS come down to personal preference, although Apache has the decided advantages of being free, providing full source code, and using an unrestricted license. Apache 2.0 is the current version you’ll be using. IIS is easier to integrate with Active Directory, Microsoft’s latest authentication system, but this applies mostly to internal company web sites.


According to the Netcraft web server survey, Apache has been the most popular web server on the Internet since April 1996.

Because web servers like Apache and IIS are made to serve up pages from HTML files, they need a way to know how to process PHP language code. Apache uses a system called modules to load extensions into its functionality. IIS uses a similar concept called ISAPI. These both allow for faster processing of the PHP code than the old school process of calling PHP as a separate executable each time the web server had a request for a page containing PHP. We’ll discuss how the Apache module is set up in Chapter 2.

Apache Versions

Apache has only two major versions in use today. They are 1.3 and 2. Apache 2 is a major rewrite and supports threading. Threads are a way for a single process to manage more than one thing at a time. The benefit is an increase in speed and a reduction in the resources needed. Unfortunately, PHP isn’t totally compatible with threading yet. Apache 2 has been out long enough to be considered stable for development and production use. Apache 2 also supports more powerful modules. Some additional modules can be found at http://www.cri.ensmp.fr/~coelho/mod_macro/. However, shared module DLLs that don’t come with the official Apache source files, such as mod_php4, mod_ssl, mod_auth_mysql, and mod_auth_ntsec, can be found on the Web.

Birth of MySQL

MySQL was developed in the 1990s to fill the ever-growing need for computers to manage information intelligently. The original core MySQL developers were trying to solve their needs for a database by using mSQL, a small and simple database. It become clear that mSQL couldn’t solve all the problems they wanted it to, so they created a more robust database that turned into MySQL

MySQL supports several different database engines. The database engine determines how MySQL handles the actual storage and querying of the data. Because of that, each storage engine has its own set of abilities and strengths. Over time, the database engines available are becoming more advanced and faster. Table 1-1 lists when various features have been added to MySQL.

Table 1-1. Major MySQL releases




The MyISAM database engine is added and is the default engine. It handles large amounts of data efficiently.

The InnoDB database engine debuts for transaction safe database processing and support for foreign keys. Foreign keys allow the relationships between tables to be explicitly designated in the database.


Queries support unions. Unions allow merging the results of two queries into one result. Configuration changes can be made without restarting the database.


A help command is included for the database client. There is support for unnamed views, also known as subqueries. Unnamed views allow you to treat a query like a separate table within a query.


Database triggers, stored procedures, and cursors are added. A trigger allows code to run in the database when a triggering event occurs, such as inserting data into a table. Stored procedures allow programs to be defined and executed within the database. Cursors allow code in the database to be run for each row that matches a query.


Constraints and partitioning are added. Constraints are used to define rules for when rows can be added or modified in the database. Partitioning is used to split up the physical storage of large tables based on a defined rule. It is commonly used to increase the performance of large tables such as historical data.

The current production release of MySQL is the latest available 5.0x version. MySQL 5.0 provides performance that is comparable to any of the much more expensive enterprise databases such as Oracle, Informix, DB2 (IBM), and SQL Server (Microsoft). The developers have achieved this level of performance by leveraging the talents of many open source developers, along with community testing. For general web-driven database tasks, the default MyISAM database engine works perfectly fine.


The newest advanced features of MySQL 5.1 are not as stable as features introduced in prior releases. MySQL 5.0 is the current stable general release. Download the latest minor release (the largest of the third portion of the version number) for whichever major version you choose. It has the most bug fixes for that version included.

Apache also has the advantage of being able to run on operating systems other than Windows, which now brings us to the subject of compatibility.


Web browsers like Firefox, Netscape, and Internet Explorer are made to process HTML, so it doesn’t matter what operating system a web server runs on. Apache, PHP, and MySQL support a wide range of operating systems, so you aren’t restricted to a specific OS on either the server or the client. While you don’t have to worry much about software compatibility, the sheer variety of file formats and different languages that all come together does take some getting used to.

Integrating Many Sources of Information

In the early days of the Web, life was simple. There were files that contained HTML and binary files such as images. Several technologies have since been developed to organize the look of web pages. For example, Cascading Style Sheets (CSS) pull presentation information out of your HTML and into a single spot so that you can make formatting changes across an entire set of pages all at once; you don’t have to manually change your HTML markup one HTML page at a time.

You can potentially have information coming from HTML files that reference CSS, PHP templates, and a MySQL database all at once. PHP templates make it easier to change the HTML in a page when it contains fields populated by a database query. We’ll briefly discuss each of these information sources.

MySQL Database

MySQL is a relational database management system that stores data in separate tables rather than putting all the data in one spot. This adds flexibility, as well as speed. The SQL part of MySQL stands for Structured Query Language, which is the most common language used to access every type of database in existence. Just to give you a taste of what your code will look like, Example 1-1 is an example of MySQL code called from PHP for deleting a user from the MySQL database.

Example 1-1. A PHP function to delete a user from the user_name database table

// A function to delete a user from the site_user table based on
//the $user_name parameter.
// An open database connection is assumed

function remove_user($user_name){
    // Remove a User
    // This is the SQL command
    $sql_delete = "DELETE FROM `site_user` WHERE `User`='$user'";
    $success = mysql_query($sql_delete) or die(mysql_error());

    // print the page header
                <title>Remove User</title>
                <link rel="stylesheet" type="text/css" href="user_admin.css" />
                <div class="user_admin">');

    // Check to see if the deletion was sucessful
    if ($success){
        // Tell the user it was sucessful
        print("The account for $user_name was deleted successfully.");
    else {
        // Tell the user it was not sucessful
        print("User $user could not be deleted. Please try again later.");

    // Print the page footer


Don’t worry about understanding precisely what’s happening in Example 1-1. The idea is simply to realize that there’s PHP code, database code, and a link to a stylesheet.

PHP Templates

To simplify the maintenance of sites that have many different pages, but that all have a common look, the header and footer of each page can be placed in a separate file and included in each PHP page. This allows changes to the header or footer to be made in one location, but not to change the look of every page automatically, which frees the developer from having to modify every single page on the web site.

PHP developers have learned that separating the PHP code from HTML can make life easier for both developers and business users who know how to modify HTML but don’t understand PHP very well. By creating separate PHP template files that have placeholders for dynamic data, you can separate the HTML markup from the PHP code.

Example 1-2 shows an example template file.

Example 1-2. A PHP template
        <title>My Books</title>
        <p>Favorite Books:</p>
            Title: {$title}<br />
            Author: {$author}

When the page is processed by the PHP engine, the placeholders are replaced with their associated values, as shown in Example 1-3.

Example 1-3. The resulting HTML code after template substitution and processing
        <title>My Books</title>
        <p>Favorite Books:</p>
            Title: Java in a Nutshell<br />
            Author: Flanagan

The result is that while you’ve added another file to the mix, you’ve made the HTML markup easier to read, and the PHP code is less cluttered with extraneous HTML. A web developer who’s not skilled in PHP can modify the look of the page without worrying about breaking the PHP code.

The last type of information we discuss also comes from a desire to separate the presentation styles such as colors and spacing from the core content.

Cascading Style Sheets

Cascading Style Sheets (CSS) are added to HTML to give web developers and users more control over the way their web pages display. Designers and users can create stylesheets that define how different elements, such as headers and links, appear on the web site. The term cascading derives from the fact that multiple stylesheets can be applied to the same web page. To apply CSS code, the example code shown is placed within the head of your HTML file.

        <title>CSS Example</title>
        <style type="text/css">
            h4, b {color: #80D92F; font-family: arial; }
            p { text-indent: 2cm; background: yellow; font-family: courier;}

        <h3>Learn how to use CSS on your websites!</h3>
        <h4>It's cool, it's amazing, it even saves you time!</h4>
        <p>Isn't this <b>nifty</b>?</p>

The code that begins with style is the CSS code. The document renders as shown in Figure 1-1.

CSS and HTML displayed in your browser
Figure 1-1. CSS and HTML displayed in your browser

Although we include the CSS in the file in this example, it could come from a separate file as it did in Example 1-1 as user_admin.css.

Of course, we also have plain old HTML files in the mix, too.


For more information on CSS, see Eric Meyer’s Cascading Stylesheets: The Definitive Guide, Second Edition (O’Reilly, 2004).

HTML Markup

HTML markup applies tags to content to identify information that is of a particular type, or that needs special formatting. HTML tags are always enclosed in angle brackets (<>) and are case-insensitive; so it doesn’t matter if you type in upper- or lowercase (though XHTML recommends all lowercase). Tags typically occur in begin-end pairs. These pairs are in the form

<tag>Isn't this nifty?</tag>

The first <tag> indicates the beginning of a tag-pair, and the last </tag> indicates the end. This complete pair of tags is called an element. Any content within an element has the rules of that element applied to it. In the earlier example, the text “Learn how to use CSS on your websites!” is contained by an h3 element.

<h3>Learn how to use CSS on your websites!</h3>

It’s also good practice (and it’s required by XHTML) that your tags nest cleanly to produce elements with clear boundaries. Always use end tags when you reach the end of an element, and avoid having pairs of tags that overlap. (Instead of <b>bold <i>bold italic</b> italic</i>, write <b>bold <i>bold italic</i></b> <i>italic</i>, for example.)

Requesting Data from a Web Page

How all of these pieces integrate together can be tricky to understand. If a web server detects PHP code, it determines whether the file is a PHP file, and if so, turn over the processing of the page to the PHP interpreter without any additional participation by the web browser. But if you include an external CSS file, your browser issues a separate request for that file before viewing the page.

PHP Interpretation on the Server

This processing of the PHP on the server is called server-side processing. When you request a web page, you trigger a whole chain of events. Figure 1-2 illustrates this interaction between your computer and the web server (host of the web site).

While the user only types in a URL and hits Enter, there are several steps that occur behind the scenes to handle that request
Figure 1-2. While the user only types in a URL and hits Enter, there are several steps that occur behind the scenes to handle that request

Here’s the breakdown of Figure 1-2:

  1. You enter a web page address in your browser’s location bar.

  2. Your browser breaks apart that address and sends the name of the page to the web server. For example, http://www.phone.com/directory.html would request the page directory.html from www.phone.com.

  3. A program on the web server, called the web server process, takes the request for directory.html and looks for this specific file.

  4. The web server reads the directory.html file from the web server’s hard drive.

  5. The web server returns the contents of directory.html to your browser.

  6. Your web browser uses the HTML markup that was returned from the web server to build the rendition of the web page on your computer screen.

The HTML file called directory.html (requested in Figure 1-2) is called a static web page. It is static because everyone who requests the directory.html page gets exactly the same page.

For the web server to customize the returned page, PHP and MySQL are added to the mix. Figure 1-3 illustrates the extra steps that occur in the chain of events on the web host.

The PHP interpreter, MySQL, and the web server cooperate to return the page
Figure 1-3. The PHP interpreter, MySQL, and the web server cooperate to return the page

Each step in the chain is listed here:

  1. You enter a web page address in your browser’s location bar.

  2. Your browser breaks apart that address and sends the name of the page to the host. For example, http://www.phone.com/login.php requests the page login.php from www.phone.com.

  3. The web server process on the host receives the request for login.php.

  4. The web server reads the login.php file from the host’s hard drive.

  5. The web server detects that the PHP file isn’t just a plain HTML file, so it asks another process—the PHP interpreter—to process the file.

  6. The PHP interpreter executes the PHP code that it finds in the text it received from the web server process. Included in that code are calls to the MySQL database.

  7. PHP asks the MySQL database process to execute the database calls.

  8. The MySQL database process returns the results of the database query.

  9. The PHP interpreter completes execution of the PHP code with the data from the database and returns the results to the web server process.

  10. The web server returns the results in the form of HTML text to your browser.

  11. Your web browser uses the returned HTML text to build the web page on your screen.

This may seem like a lot of steps, but all of this processing happens automatically every time a web page with PHP code is requested.

When developing dynamic web pages, you work with a variety of variables and server components, which are all important to having an attractive, easy to navigate, and maintainable web site. Next, it’s time to install the three major cogs needed to make this work: Apache, PHP, and MySQL.

Chapter 1 Questions

Question 1-1.

What three components do you need to create a dynamic web page?

Question 1-2.

What does Apache use to load extensions?

Question 1-3.

What is the current stable release of PHP?

Question 1-4.

What does the SQL part of MySQL stand for?

Question 1-5.

What are angle brackets (< >) used for?

Question 1-6.

What does the PHP Interpreter do?

See the Appendix for the answers to these questions.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required