PHP and MySQL Tutorial

Building a Database-Driven Web Site Using PHP and MySQL
by Kevin Yank of

On the Web today, content is king. After you've mastered HTML and learned a few neat tricks in JavaScript and Dynamic HTML, you can probably build a pretty impressive-looking Web site design. But then comes the time to fill that fancy page layout with some real information. Any site that successfully attracts repeat visitors has to have fresh and constantly updated content. In the world of traditional site building, that means HTML files--and lots of 'em.

The problem is that, more often than not, the people providing the content for a site are not the same people handling its design. Oftentimes, the content provider doesn't even know HTML. How, then, is the content to get from the provider onto the Web site? Not every company can afford to staff a full-time Webmaster, and most Webmasters have better things to do than copying Word files into HTML templates anyway.

Maintenance of a content-driven site can be a real pain, too. Many sites (perhaps yours?) feel locked into a dry, outdated design because rewriting those hundreds of HTML files to reflect a new design would take forever. Server-side includes (SSI's) can help alleviate the burden a little, but you still end up with hundreds of files that need to be maintained should you wish to make a fundamental change to your site.

The solution to these headaches is database-driven site design. By achieving complete separation between your site's design and the content you are looking to present, you can work with each without disturbing the other. Instead of writing an HTML file for every page of your site, you only need to write a page for each kind of information you want to be able to present. Instead of endlessly pasting new content into your tired page layouts, create a simple content management system that allows the writers to post new content themselves without a lick of HTML!

In this 10-part weekly series of articles, I'll provide a hands-on look at what's involved in building a database-driven Web site. We'll be using two new tools for this: the PHP scripting language and the MySQL relational database. If your Web host provides PHP/MySQL support, you're in great shape. If not, we'll be looking at the set-up procedures under Unix and Windows, so don't sweat it.

These articles are aimed at intermediate or advanced Web designers looking to make the leap into server-side programming. You'll be expected to be comfortable with HTML, as I'll be making use of it without explanation. A teensy bit of JavaScript may serve us well at some point, but I'll be sure to keep it simple for the uninitiated.

By the end of this series, you can expect to have a grasp of what's involved in setting up and building a database-driven Web site. If you follow along with the examples, you'll also learn the basics of PHP (a server-side scripting language that allows you to do a lot more than access a database easily) and Structured Query Language (SQL -- the standard language for interacting with relational databases). Most importantly, you'll come away with everything you need to get started on your very own database-driven site in no time!

Part 1: Installation

Welcome to the Show

Hi there, and welcome to the first in's ten-part series on building a database-driven Web site! For the next few months, it will be my job to guide you as you take your first steps beyond the HTML-and-JavaScript world of client-side site design. Together we'll learn everything that's needed to build the kind of large, content-driven sites that are so successful today, but which can be a real headache to maintain if they aren't done right.

Before we get started, we need to gather together the tools we'll need for the job. In this first article, we'll download and set up the two software packages we'll be using: PHP and MySQL.

PHP is a server-side scripting language. You can think of it as a "plug-in" for your Web server that will allow it to do more than just send plain Web pages when browsers request them. With PHP installed, your Web server will be able to read a new kind of file (called a "PHP script") that can do things like retrieve up-to-the-minute information from a database and insert it into a Web page before sending it to the browser that requested it. PHP is completely free to download and use.

To retrieve information from a database, you first need to have a database. That's where MySQL comes in. MySQL is a relational database management system, or RDBMS. Exactly what role it plays and how it works we'll get into later, but basically it's a software package that is very good at organizing and managing large amounts of information. MySQL also makes that information really easy to get at using server-side scripting languages like PHP. MySQL is free for non-commercial use on most Unix-based platforms, like Linux. MySQL for Windows 9x/NT/2000 costs about US$200 to buy, but you can download an older version for free if you just want to try it out. For our purposes, the older version will serve just fine, but if you find MySQL for Windows useful and you decide to use it on one of your own sites, you should pay for it.

If you're lucky, your current Web host may already have installed MySQL and PHP on your Web server for you. If that's the case, much of this article will not apply to you, and you can skip straight to If Your Web Host Provides PHP and MySQL to make sure everything is ship shape.

Everything we'll discuss in this article series may be done on a Windows- or Unix-based server. Depending on which type of server you'll be using, the installation procedure will be different. The next section deals with installation on a Windows-based Web server. The section after that deals with installation under Linux (and other Unix-based platforms). Unless you're especially curious, you should only need to read the section that applies to you.

Part 2: Getting Started with MySQL

Hi there, and welcome back! Last week, we went through the process of installing and setting up two software programs: PHP and MySQL. This week, we'll be concentrating on the latter by learning how to work with MySQL databases using Structured Query Language (SQL).

An Introduction to Databases

As I explained briefly last week, PHP is a server-side scripting language that lets you insert instructions into your Web pages that your Web server software (be it Apache, Personal Web Server, or whatever) will execute before sending those pages to a browser that requests them. In a brief example, I showed how it was possible to insert the current date into a Web page every time it was requested.

Now that’s all well and good, but things really get interesting when a database is added to the mix. A database server (in our case, MySQL) is a program that can store large amounts of information in an organized format that is easily accessible from scripting languages like PHP. For example, you could tell PHP to look in the database for a list of jokes that you’d like to appear on your Web site.

In this example, the jokes would be stored entirely in the database. The advantage of this would be twofold. First, instead of having to write an HTML file for each of your jokes, you could write a single PHP file designed to fetch any joke out of the database and display it. Second, to add a joke to your Web site would just be a matter of adding the joke to the database. The PHP code would take care of the rest by automatically displaying the new joke along with the rest when it fetched the list of jokes from the database.

Let’s run with this example as we look at how data is stored in a database. A database is composed of one or more 'tables', each of which contains a list of 'things'. For our joke database, we would probably start with a table called "jokes" which would contain a list of jokes. Each table in a database has one or more columns, or fields. Each column holds a certain piece of information about each "thing" in the database. Returning to our example, our "jokes" table might have columns for the text of the jokes and the dates the jokes were added to the database. Each joke that we stored in this table would then be said to be a 'row' in the table. To see where all this terminology comes from, have a look at what this table actually looks like:

Notice that, in addition to columns for the joke text ("JokeText") and the date of the joke ("JokeDate"), I included a column named "ID". The function of this column is to assign a unique number to each joke so we have an easy way to refer to them and to keep track of which joke is which.

Part 3: Getting Started with PHP

Last week, we learned how to use the MySQL database engine to store a list of jokes in a simple database (composed of a single table named
Jokes). To do so, we used the MySQL command line client to enter SQL commands (queries). This week, we'll introduce the PHP server-side scripting language. In addition to the basic features we'll be looking at this week, this language has full support for communicating with MySQL databases.

Presenting PHP

As we've discussed previously, PHP is a server-side scripting language. This concept is not obvious, especially if you're just used to designing pages with HTML and JavaScript. A server-side scripting language is similar to JavaScript in many ways, as they both allow you to embed little programs (scripts) into the HTML of a Web page. In executing, such scripts allow you to control what will actually appear in the browser window in some way more flexible that what is possible using straight HTML.

The key difference between JavaScript and PHP is that, while the Web browser interprets JavaScript once the Web page containing the script has been downloaded, server-side scripting languages like PHP are interpreted by the Web server before the page is even sent to the browser. Once interpreted, the PHP code is replaced in the Web page by the results of the script, so all the browser sees is a standard HTML file. The script is processed entirely by the server. Thus the designation: server-side scripting language.

Let's look back at the
today.php example presented in Part One:

<TITLE>Today's Date</TITLE>
<P>Today's Date (according to this Web server) is

echo( date("l, F dS Y.") );


Most of this is plain HTML. The line between
<?php and ?>, however, is written in PHP. <?php means "begin PHP code", and ?> means "end PHP code". The Web server is asked to interpret everything between these two delimiters and convert it to regular HTML code before sending the Web page to a browser that requests it. The browser is presented with something like this:

<TITLE>Today's Date</TITLE>
<P>Today's Date (according to this Web server) is
Wednesday, June 7th 2000.</BODY>

Notice that all signs of the PHP code have disappeared. In their place, the output of the script has appeared and looks just like standard HTML. This example demonstrates several advantages of server-side scripting:

* No browser compatibility issues. PHP scripts are interpreted by the Web server and nothing else, so you don't have to worry about whether the language you're using will be supported by your visitors' browsers.

* Access to server-side resources. In the above example, we place the date according to the Web server into the Web page. If we had inserted the date using JavaScript, we would only be able to display the date according to the computer on which the Web browser was running. Now while this isn't an especially impressive example of exploiting server-side resources, we could have just as easily inserted some other information that would only be available to a script running on the Web server—information stored in a MySQL database running on the Web server computer, for example.

* Reduced load on the client. JavaScript can significantly slow down the display of a Web page on slower computers, as the browser must run the script before it can display the Web page. With server-side scripting, this becomes the burden of the Web server machine to bear.

So to review, the above is a three-column table with two rows (or entries). Each row in the table contains a joke's ID, its text, and the date of the joke. With this basic terminology under our belts, we're ready to get started using MySQL.

Part 4: Publishing MySQL Data on the Web

This is it -- the stuff you signed up for! This is the week we take information stored in a database and display it on a Web page for all to see. So far we've installed and learned the basics of MySQL, a relational database engine, and PHP, a server-side scripting language. In this week's installment, we see how to use these two new tools together to create a true database-driven Web site!

A Look Back at First Principles

Before we leap forward, it's worth a brief look back to remind ourselves of the goal we are working toward. We have two powerful, new tools at our disposal: the PHP scripting language, and the MySQL database engine. It's important to understand how these two are going to fit together.

The whole idea of a database-driven Web site is to allow the content of the site to reside in a database, and for that content to be dynamically pulled from the database to create Web pages featuring it for people using a regular Web browser to view. So on one end of the system you have a visitor to your site using a Web browser, loading, and expecting to view a standard HTML Web page. On the other end you have the content of your site sitting in one or more tables in a MySQL database that only understands how to respond to SQL queries (commands).

The PHP scripting language is the go-between that speaks both languages. Using PHP, you can write the presentation aspects of your site (the fancy graphics and page layouts) as "templates" in regular HTML. Where the content belongs in those templates, you use some PHP code to connect to the MySQL database and -- using SQL queries just like those you used to create a table of jokes in Part Two -- retrieve and display some content in its place.

Just so it's clear and fresh in your mind, this is what will happen when someone visits a page on our database-driven Web site:

* The visitor's Web browser asks for the Web page using a standard URL.
* The Web server software (Apache, IIS, or whatever) recognizes that the requested file is a PHP script, and so interprets it using its PHP plug-in before responding to the page request.
* Some PHP commands (which we have yet to learn) connect to the MySQL database and request the content that belongs in the Web page.
* The MySQL database responds by sending the requested content to the PHP script.
* The PHP script stores the content into one or more PHP variables, then uses the now-familiar
echo function to output it as part of the Web page.
* The PHP plug-in finishes up by handing a copy of the HTML it has created to the Web server.
* The Web server sends the HTML to the Web browser as it would a plain HTML file, except instead of coming directly from an HTML file, the page is the output provided by the PHP plug-in.

Part 5: Relational Database Design

Since Part Two of this series, we've been working with a very simple database of jokes, composed of a single table named, appropriately enough,
Jokes. While this database has served us well as an introduction to using MySQL databases, there is a lot more to relational database design than this simple example illustrates. This week, we'll expand on our example and learn about a few new features of MySQL in an effort to realize and appreciate what relational databases have to offer.

Be forewarned that many topics will be covered only in an informal, hands-on (i.e. non-rigorous) sort of way. As any computer science major will tell you, database design is a serious area of research with tested and mathematically provable principles that, while useful, are beyond the scope of this article. For more information, I would recommend stopping by for a list of good books, as well as several useful resources on the subject.

Giving Credit where Credit is Due

To start things off, let's recall the structure of our
Jokes table. It contains three columns: ID, JokeText, and JokeDate. Together, these columns allow us to identify jokes (ID), keep track of their text (JokeText) as well as the date they were entered (JokeDate).

Now let's say we wanted to track another piece of information about our jokes: the names of the people who submitted them. It would seem natural to want to add a new column to our
Jokes table for this. The SQL ALTER command (which we have not seen before) lets us do exactly what we need. Log into your MySQL server using the mysql command-line program as in Part Two, select your database (jokes if you used the name suggested in Part Two) then type the following command:

-> AuthorName VARCHAR(100);

This adds a column called
AuthorName to our table. The type declared is a variable-length character string of up to 100 characters in length (plenty for even very esoteric names). Let's also add a column for the author's e-mail address:

-> AuthorEMail VARCHAR(100);

For more information about the
ALTER command, see the MySQL Reference Manual. Just to make sure the two columns were added properly, we should ask MySQL to describe the table to us:

mysql> DESCRIBE Jokes;
+-------------+--------------+------+-----+-- - -
| Field | Type | Null | Key | Def...
+-------------+--------------+------+-----+-- - -
| ID | int(11) | | PRI | ...
| JokeText | text | YES | | ...
| JokeDate | date | | | ...
| AuthorName | varchar(100) | YES | | ...
| AuthorEMail | varchar(100) | YES | | ...
+-------------+--------------+------+-----+-- - -
5 rows in set (0.01 sec)

Looks good. Obviously, we would need to make changes to the HTML and PHP form code we created in Part Four for adding new jokes to the database, but I'll leave figuring out those details to you as an exercise. Using
UPDATE queries, we could now add author details to all the jokes in the table. Before getting carried away with this, however, we need to stop and consider if we made the right design choice here. In this case, it turns out that we did not.

Part 6: A Content Management System

So far, we've seen several examples of database-driven Web pages: pages that display information culled from a MySQL database when the page is requested. Until now, however, we haven't seen a solution that would be much more manageable than raw HTML files when scaled up to encompass a Web site as large and complex as Sure, our Internet Joke Database was nice, but when it came to managing categories and authors, we would always be returning to the MySQL command line, trying to remember complicated
SELECT and INSERT statements, as well as table and column names, to accomplish the most menial of tasks.

To make the leap from a Web page that displays information stored in a database to a complete database-driven Web site, we need to add a content management system. Such a system usually takes the form of a series of Web pages, access to which is restricted to users authorized to make changes to the Web site. These pages provide a database administration interface, allowing a user to easily view and change the information stored in the database without messing with the mundane details of SQL syntax.

The beginnings of a content management system were seen at the end of Part Four, where we allowed site visitors to add jokes to and (if you worked through the challenge) delete jokes from the database using a Web-based form and a "delete this joke" link, respectively. While impressive, these are not features you would normally include in the interface presented to casual site visitors. For example, you don't want someone to be able to add offensive material to your Web site without your knowledge. And you definitely don't want just anyone to be able to delete jokes from your site.

By relegating those "dangerous" features to the restricted-access site administration pages, you avoid the risk of exposing your data to just anyone while maintaining the power of being able to manage the contents of your database without having to memorize SQL queries. In this part of the series, we'll expand on the capabilities of our joke management system to take advantage of some of the enhancements we made to our database in Part Five. Specifically, we'll allow a site administrator to manage authors and categories, and assign these to corresponding jokes.

As explained above, these administration pages must be protected by an appropriate access restriction scheme. Placing the corresponding PHP files into a directory protected by an Apache-style .htaccess file listing authorized users would be one way of doing this. Consult your Web server's documentation for information on restricting access to Web pages.

Since we'll be working with some fairly large PHP files in this part, it will be necessary to gloss over some of the details due to space constraints. The complete code of all the files discussed in this part of the series, which together form the complete joke management system, is provided in a source code archive, which may be downloaded by clicking here ( - 9.8KB).

The Front Page
As of the end of Part Five, our database contained tables for three types of entities: jokes, authors, and joke categories. Note that we are sticking with our original assumption of one email address per author, so we won't have a separate table for email addresses. The front page of our content management system, therefore, will contain links to pages for managing each of these three things:

<!-- admin.html -->
<H1>Joke Management System</H1>
<LI><A HREF="jokes.php">Manage Jokes</A>
<LI><A HREF="authors.php">Manage Authors</A>
<LI><A HREF="cats.php">Manage Joke Categories</A>

Part 7: Content Formatting and Submission

We're almost there. We've designed a database for storing jokes, organizing them into categories, and tracking their authors. We've learned how to create a Web page that displays this library of jokes to site visitors. We've even developed a set of Web pages that a site administrator can use to manage the joke library without having to know anything about databases.

In so doing, we've removed the headaches of continually plugging new content into a tired HTML page template, and creating an unmanageable mass of HTML files. The HTML is now kept completely separate from the data it displays. If you want to redesign the site, you just have to make the changes to the HTML contained in the PHP files that site visitors see. A change to one file (e.g. changing a font) is immediately reflected in the page layouts of all jokes, because all jokes are displayed using that single PHP file. Only one task still requires HTML to enter into the equation for managing the content of the Web site: content formatting.

On any but the simplest of Web sites, it will be necessary to allow content (in this case, jokes) to have formatting applied to them. In the simple case, this may just be the ability to break text into paragraphs. Often, however, content providers will expect facilities such as boldfaced or italicized text, hyperlinks, etc.

Our current database and site design supports all of this, since a site administrator can include HTML tags in the text of a joke, and these will have their usual effects when the joke text is inserted into the page that a site visitor's browser requests. However, to achieve our goal of eliminating HTML from the system entirely, we must provide some other way of formatting text.

In this part of our series, we'll learn some new PHP functions that will enable us to provide basic text formatting without the use of HTML. In so doing, we'll have completed a content management system easy enough for anyone with a Web browser to use. We'll then take full advantage of this ease of use by allowing site visitors to once again submit their own jokes -- this time without the risk of our site becoming filled with obscene or otherwise inappropriate material.

Part 8: MySQL Administration

At the core of any well-designed, content-driven site is a relational database. In this series, we've used the MySQL Relational Database Management System (RDBMS) to create our database. MySQL is a popular choice among Web developers not only because it is free for non-commercial use on all platforms under the GPL, but also because it is fairly simple to get a MySQL server up and running. As we demonstrated in Part One of this series, armed with proper instructions a new user can get a MySQL server up and running in less than 30 minutes (less than 10 if you practice a little!).

If all you want to do is have a MySQL server around so you can play with a few examples and experiment a little, then the initial installation process we went thought in Part One is likely to be all you'll need. If, on the other hand, you want to set up a database backend to a real, live Web site -- perhaps a site upon which your company depends -- then there are a few more things you'll need to learn how to do before relying on a MySQL server day-in and day-out.

Backups of data important to you or your business should be part of any Internet-based enterprise. Unfortunately, since setting up backups isn't the most interesting part of an administrator's duties, such procedures are usually arranged once out of necessity and can be deemed "good enough" for all applications. If your answer to "Should we be backing up our databases?" up to now has been "It's okay; they'll be backed up along with everything else." then you should really stick around. We'll be seeing why a generic file backup solution is inadequate for many MySQL installations, and we'll be demonstrating the "right way" to back up and restore a MySQL database.

In Part One, we set up the MySQL server so that you could connect as 'root' with a password of your choosing. This 'root' MySQL user (which, incidentally, has nothing to do with the Unix 'root' user) had read/write access to all databases and tables. In many organizations, it can be necessary to create other users with access to only particular databases and tables, and restrict that access in some way (e.g. read-only access to a particular table). In this part, we'll be learning how this can be done using two new MySQL commands:

In some situations, such as power outages, MySQL databases can become damaged. Such damage need not always send you scrambling for your backups, however. We'll finish off our look at MySQL database administration by learning how to use the MySQL database check and repair utility to fix simple database corruptions.

Part 9: Advanced SQL

As we worked through our example of an Internet Joke Database website, we had opportunities to explore most aspects of Structured Query Language (SQL). From the basic form of a
CREATE TABLE query, to the two syntaxes of INSERT queries, you probably know many of these commands by heart by now.

This week, in an effort to tie up loose ends, we'll be looking at a few more SQL tricks that we haven't seen before, either because they were too advanced, or simply because "it didn't come up". As is typical, most of these will expand on our knowledge of what is already the most complex and potentially confusing SQL command available to us: the
SELECT query.

SELECT Query Results

Long lists of information are always easier to use when they are provided in some kind of order. Finding a single author in a listing of our
Authors table, for example, could become an exercise in frustration if we had more than a few dozen registered authors in our database. While at first it might appear that they are sorted in order of database insertion (with the oldest records first and the newest records last), you'll quickly notice that deleting records from the database leaves invisible gaps in this order, which get filled in by newer entries as they are inserted.

What this amounts to is no reliable built-in sorting of results from
SELECT queries. Fortunately, there is another optional part of the SELECT query that lets us specify a column by which to sort our table of results. Let's say we wanted to print out a listing of the entries in our Authors table for future reference. If you'll recall, this table has three columns: ID, Name, and eMail. Since ID isn't really interesting in and of itself (it just provides a means to associate entries in this table with entries in the Jokes table), we will usually just list the remaining two columns when working with this table. Here's a short listing of a table of authors:

mysql> SELECT Name, eMail FROM Authors;
| Name | eMail |
| Joan Smith | |
| Ted E. Bear | |
| Kevin Yank | |
| Amy Mathieson | |

As you can see, the entries are sorted in no particular order. This is fine for a short list like this, but if we were looking for a particular author's email address (that of Amy Mathieson, for example) in a very long list of authors (say a few hundred or so), having the authors' names appear in alphabetical order would make finding the entry quite a bit easier. Here's how:

mysql> SELECT Name, eMail FROM Authors ORDER BY Name;
| Name | eMail |
| Amy Mathieson | |
| Joan Smith | |
| Kevin Yank | |
| Ted E. Bear | |

The entries now appear sorted alphabetically by their names. Just as we can add a
WHERE clause to a SELECT statement to narrow down the list of results, we can also add an ORDER BY clause to specify a column by which a set of results should be sorted.

By adding the keyword
DESC following the name of the sort column, you can have the entries sorted in descending order:

mysql> SELECT Name, eMail FROM Authors ORDER BY Name DESC;
| Name | eMail |
| Ted E. Bear | |
| Kevin Yank | |
| Joan Smith | |
| Amy Mathieson | |

You can actually use a comma-separated list of several column names in the
ORDER BY clause to have MySQL sort the entries by the first column, then sort any sets of tied entries by the second, and so on. Any of the columns listed in the ORDER BY clause may use the DESC keyword to reverse the sort order.

Part 10: Advanced PHP

PHP's strength lies in its huge library of built-in functions, which allow even a novice user to perform very complicated tasks without having to install new libraries or worry about low-level details, as is often the case with other popular server-side languages like Perl. Because of the focus of this series, we've constrained ourselves to exploring only those functions that were directly related to interacting with a MySQL database (in fact, we didn't even see all of those). In this final installment, we'll broaden our horizons a little and see some of the other useful features PHP has to offer someone building a database driven Web site.

We'll begin by learning about PHP's
include function, which allows us to use a single piece of PHP code in multiple pages, making the use of common code fragments much more practical. We'll also see how to add an extra level of security to our site using this feature.

PHP, while generally quick and efficient, nevertheless adds to the load time and the workload of the machine the server is running on. On high-traffic sites (, for example!), this load can grow to unacceptable levels. But this doesn't mean we have to abandon the database-driven nature of our site. We'll see how to use PHP behind the scenes to create semi-dynamic pages that don't stress the server as much.

A common question asked on and in other sites' forums is how to use an
<INPUT TYPE=FILE> tag to accept file uploads from site visitors. We'll learn how to do this with PHP, and see how to make this fit in with the database-driven nature of our site.

Finally, an extremely powerful feature of PHP is the ability to easily send email messages with dynamically generated content. Whether you want to use PHP to let visitors send email versions of your site's content to their friends, or just provide a way for users to retrieve their forgotten passwords, PHP's
email function will serve nicely!