23.1 Building HTML Tables from SQL Queries

Perhaps the simplest task you can perform with a database and PHP is to extract data from a table and display it in an HTML table. The table could contain a catalog of items for sale, a list of projects, or a list of Internet name servers and their ping times. For illustration purposes, I'll use the first scenario. Imagine that a supermarket wants to list the items it has for sale on its Web site. As a proof of concept, you must create a page that lists some items from a database. We'll use the test database that's created when MySQL is installed. The PHP script for viewing the catalog of products will reside on the same machine as the database server.

The first step is to create the table. Listing 23.1 displays some SQL code for creating a simple, three-column table. The table is named catalog. It has a column called ID that is an integer with at most 11 digits. It cannot be null, and new rows will automatically be assigned consecutive values. The last line of the definition specifies ID as a primary key. This causes an index to be built on the column and disallows duplicate IDs. The other two columns are Name and Price.

Listing 23.1 Creating catalog table

CREATE TABLE catalog
(
    ID INT(11) NOT NULL AUTO_INCREMENT,
    Name CHAR(32),
    Price DECIMAL(6,2),

    PRIMARY KEY (ID)
);

Name is a character string that may be up to 32 characters long. Price is a six-digit number with two decimal places, which is a good setup for money. Next, we will need to put some items in the table. Since we're only creating a demo, we'll fill in some items we might expect in a supermarket along with some dummy prices. To do this we'll use the INSERT statement. Listing 23.2 is an example of this procedure.

Listing 23.2 Inserting data into catalog table

INSERT INTO catalog (Name, Price) VALUES
    ('Toothbrush', 1.79),
    ('Comb', 0.95),
    ('Toothpaste', 5.39),
    ('Dental Floss', 3.50),
    ('Shampoo', 2.50),
    ('Conditioner', 3.15),
    ('Deodorant', 1.50),
    ('Hair Gel', 6.25),
    ('Razor Blades', 2.99),
    ('Brush', 1.15);

Each SQL statement ends with a semicolon, much as in PHP. We're telling the MySQL server that we want to insert a number of rows into the catalog table, and we'll be supplying only the name and price. Since we're leaving out ID, MySQL creates one. This is due to our defining the column as AUTO_INCREMENT. The VALUES keyword lets the server know we are about to send the values we promised earlier in the command. Notice the use of single quotes to surround text, as is standard in SQL. MySQL allows inserting multiple rows in one statement by separating rows with commas. Most other database servers require a separate statement for each row.

Just to check that everything went well, Figure 23.1 shows the output you would get if you selected everything from the catalog table from within the MySQL client. I got this output by typing SELECT * FROM catalog; in the MySQL client.

Figure 23.1 SELECT * FROM catalog.

+----+--------------+-------+
| ID | Name         | Price |
+----+--------------+-------+
|  1 | Toothbrush   |  1.79 |
|  2 | Comb         |  0.95 |
|  3 | Toothpaste   |  5.39 |
|  4 | Dental Floss |  3.50 |
|  5 | Shampoo      |  2.50 |
|  6 | Conditioner  |  3.15 |
|  7 | Deodorant    |  1.50 |
|  8 | Hair Gel     |  6.25 |
|  9 | Razor Blades |  2.99 |
| 10 | Brush        |  1.15 |
+----+--------------+-------+
10 rows in set (0.00 sec)

The last step is to write a PHP script that gets the contents of the table and dresses it up in an HTML table. Listing 23.3 lists PHP code for extracting the name and price values, then displaying them in an HTML table. The output is shown in Figure 23.2. The first step in communicating with a database server is to connect to it. This is done with the mysql_connect function. It takes a hostname, a username, and a password. I usually create a user named httpd in my MySQL databases with no password. I also restrict this user to connections made from the local server. I name it after the UNIX user who will be executing the scripts—in other words, the Web server. If you are renting space from a hosting service, you may have a MySQL user and database assigned to you, in which case you'll need to modify the function arguments, of course.

Listing 23.3 Creating HTML table from a query

<?php
    //connect to server, then test for failure
    if(!($dbLink = mysql_connect("localhost", "httpd", "")))
    {
        print("Failed to connect to database!<br>\n");
        print("Aborting!<br>\n");
        exit();
    }

    //select database, then test for failure
    if(!($dbResult = mysql_query("USE test", $dbLink)))
    {
        print("Can't use the test database!<br>\n");
        print("Aborting!<br>\n");
        exit();
    }

    // get everything from catalog table
    $Query = "SELECT Name, Price " .
        "FROM catalog " .
        "ORDER BY Name ";
    if(!($dbResult = mysql_query($Query, $dbLink)))
    {
        print("Couldn't execute query!<br>\n");
        print("MySQL reports: " . mysql_error() . "<br>\n");
        print("Query was: $Query<br>\n");
        exit();
    }

    //start table
    print("<table border=\"0\">\n");

    //create header row
    print("<tr>\n");
    print("<td bgcolor=\"#cccccc\"><b>Item</b></td>\n");
    print("<td bgcolor=\"#cccccc\"><b>Price</b></td>\n");
    print("</tr>\n");

    // get each row
    while($dbRow = mysql_fetch_assoc($dbResult))
    {
        print("<tr>\n");

        print("<td>{$dbRow['Name']}</td>\n");
        print("<td align=\"right\">{$dbRow['Price']}</td>\n");

        print("</tr>\n");
    }

    //end table
    print("</table>\n");
?>

If the connection is successful, a MySQL link identifier will be returned. Notice that I'm testing for failure and performing the connection on one line. The function used to connect to the database is mysql_connect. If you've flipped through the descriptions of the MySQL functions in Chapter 17, you might remember another function called mysql_pconnect. These two functions operate identically inside a script, but mysql_pconnect returns persistent connections.

Most of the database functions that PHP offers incorporate the idea of a persistent connection—a connection that does not close when your script ends. If the same Web process runs another script later that connects to the same database server, the connection will be reused. This has the potential to save overhead. In practice, the savings are not dramatic, owing to the way Apache 1.3.x and earlier use child processes instead of threads. These processes serve a number of requests and then are replaced by new processes. When a process ends, it takes its persistent connection with it, of course.

The next step is to select a database. Here I've selected the database named test. Once we tell PHP which database to use, we get all rows from the catalog table. This is done with the mysql_query function. It executes a query on the given link and returns a result identifier. We will use this result identifier to fetch the results of the query.

Before we begin pulling data from the results, we must begin building an HTML table. This is done, as you might expect, by using an opening table tag. I've created a header row with a gray background and left the rest of the table behavior as default.

Now that the header row is printed, we can fetch each row from the result set. The fastest way to do this, executionwise, is to use mysql_fetch_assoc. This expresses each column in the result as an element of an associative array. The names of the columns are used for the keys of the array. You could also use mysql_fetch_row or mysql_fetch_object, which are equally efficient. You should avoid mysql_result, since this function does a costly lookup into a two-dimensional array.

When no more rows remain, FALSE will be returned. Capitalizing on this behavior, I put the fetch of the row inside a while loop. I create a row in the HTML table, printing object properties inside the table cells. When no rows remain, I close the table. I don't bother to close the connection to the database because PHP will do this automatically.

This is an extremely simple example, but it touches on all the major features of working with a database. Since each row is created in a loop, each is uniform. If the data change, there is no need to touch the code that turns them into HTML. You can just change the data in the database.

A good example of this technique in action is the Random Band Name Generator <http://www.leonatkinson.com/random/index.php?SCREEN=band>, which creates random band names from a table of words stored in a MySQL database to which anyone can add. Each refresh of the page fetches another ten names.