Building HTML Tables from SQL Queries PHP

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 they have for sale on their 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 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. Name is a variable-length character string that may be up to 32 characters long. Price is a floating-point number with six digits before the decimal point and two digits after. That's a perfect 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. Each SQL statement ends with a semicolon, much as in PHP. We're telling the MySQL server that we want to insert a row 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.

Creating Catalog Table

Name VARCHAR(32),
Price FLOAT(6,2),

Inserting Data into Catalog Table

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

Just to check that everything went well, 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.

SELECT * FROM catalog


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.01 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 lists PHP code for extracting the name and price values, displaying them in an HTML table. The first step in communicating with a database server is to connect to it. This is done with the mysql_pconnect 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.

Creating HTML Table from a Query

Creating HTML Table from a Query

Creating HTML Table from a Query

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. Link identifiers are always greater than zero, and zero is returned when the connection cannot be made. So, testing for a FALSE return value allows us to detect a failed connection. If that happens, we just abort the entire script. The function used to connect to the database is 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 the process ends, it takes its persistent connection with it, of course.

Only under high loads will your script benefit from persistent connections, but that's exactly the time when it needs to benefit from them. sing mysql_pconnect costs nothing, so I use it by default. At the time of this writing, Apache 2.0 is nearing release. It promises a multithreaded approach that will certainly take full advantage of persistent connections.

The next step is to select a database. Here I've selected the database named store. 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_object. This expresses each column in the result as the property of an object. The names of the columns are used for the names of the properties. You could also use mysql_fetch_row or mysql_fetch_array, which are equally efficient. Most of the time using an object seems more readable to me. 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 simply 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 it into HTML. You can just change the data in the database.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

PHP Topics