Web Dev> Populate PHP/HTML table from MySQL database

Hey again all, for this post I’ll be covering how to populate a PHP/HTML table by way of looping through a table in mysql. I’ll be using the sample database provided by http://www.mysqltutorial.org/mysql-sample-database.aspx which has to do with models (cars, planes, ships, etc). Everyone has differing levels of knowledge so I’ll be including some basics as well such as connecting to the mysql database (and closing it later on).

The table we’ll be using in the database is ‘products’. It has the following columns:

productCode - A unique inventory number
productName - Name of the product
productLine - Basic descriptor, 'Motorcycles', 'Classic Cars', etc
productScale - This models scale size
productVendor - Company that built the model
productDescription - Detailed description of product
quantityInStock - Current number of quantity in stock
buyPrice - Listed price on the "website"
MSRP - Manufacturers Suggested Retail Price

If I know I’m going to be using my mysql database in multiple files I’ll always throw the connection in something like a ‘dbconnect.php’ file. Here’s an example:

<?php
mysql_connect(DBHOST, DBUSER, DBPASS) or die(mysql_error());
mysql_select_db(DBNAME) or die(mysql_error());
?>

Now you can include this in every file, or better yet in your header file which will get included everywhere else. So for example in your header.php file you could throw in:

<?php
require_once('dbconnect.php');
?>

Alright, so now you’ve got your connection to your database and the appropriate database selected. We’ll skip over the other content that you want to eventually add and say (for this example) that we want to list all of our models. We’ll look at doing this a few different ways, first off we’ll go simple and just request everything from the database and then we’ll tell php how to spit that all out to us.

So now in our file we’ll want to first build the sql command, then tell it to run the command, and finally we’ll use a while loop to run through each result.

This can be written a few different ways but the following is typically how I do it:

<?php
$sql = "SELECT * FROM products";
$result = mysql_query($sql)or die(mysql_error());
>

Ok, so now we’ve specified the query to run by setting the variable $sql, and followed that by running our query and populating $result with it. So now php has all the information and its stored in the $result array. What now? Now we want to build our output using php and html. Here we’ll build on what we have.

<?php
// For the purpose of saving space on my post I'm going to limit the amount of results to 4, see "LIMIT 4" below.
$sql = "SELECT * FROM products LIMIT 4";
$result = mysql_query($sql)or die(mysql_error());
// Now let's start building our table, I'll go line by line just like HTML so it's easier to read.
// I don't think we need every one of the details about each model so I'm going to just include what
// I think my customer needs to know on a product overview page. This will just list the name, description,
// quantity in stock, scale, and price of each item. We need to put the table opening tag and first row
// outside of the while loop first otherwise every time we process a row we'll get a new table and tr.
echo "<table>";
echo "<tr><th>Name</th><th>Description</th><th>Model Scale</th><th># In Stock</th><th>Price</th></tr>";
while($row = mysql_fetch_array($result)){
// Before we close out of PHP, lets define all of our variables so they are easier to remember and work with,
// you can skip this though if you just want to directly reference each row.
$name = $row['productName'];
$scale = $row['productScale'];
$desc = $row['productDescription'];
$quantity = $row['quantityInStock'];
$price = $row['buyPrice'];
// Now for each looped row
echo "<tr><td style='width: 200px;'>".$name."</td><td style='width: 600px;'>".$desc."</td><td>".$scale."</td><td>".$quantity."</td><td>".$price."</td></tr>";
} // End our while loop
echo "</table>

We should now have something like the following if we did everything correctly:

Now you would be free to go back and change the style and such as you like, maybe give each td a little room to breath by adding some cellpadding or cellspacing or whatever you like. You can also go back and change your $sql to be more specific. Say for example you only wanted to list products that had a scale of 1:12. You could change your $sql to the following:

$sql = "SELECT * FROM products WHERE productScale='1:12'";

You would then see:

Once you’ve mastered doing while loop you could add another one above it. For example what you could do would be to get the result of all products, while loop through that, and then while you are in that while loop you could use another while loop inside of it to iterate through each unique scale.

This will be a large example so I’ll just include a link to it so you can see it all in one place.
Nested Whiles

The code for that is here:

<style>
th { text-align: left; }
td { padding: 5px; border-bottom: 1px solid black; border-collapse: collapse; border-spacing: 0px; }
table { border: 2px solid black; }
</style>
<?php
mysql_connect("localhost", "MYSQL_USERNAME", "MYSQL_PASSWORD") or die(mysql_error());
mysql_select_db("MYSQL_DATABASE") or die(mysql_error());
$sql = "SELECT DISTINCT(productScale) FROM products";
$result = mysql_query($sql)or die(mysql_error());
while($info = mysql_fetch_array($result)){
$scale = $info['productScale'];
$sql_scale = "SELECT * FROM products WHERE productScale='".$scale."'";
$result_scale = mysql_query($sql_scale)or die(mysql_error());
echo "<div style='width: 100%; text-align: center;'>";
echo "<h1>".$scale." Models</h1>";
echo "<table style='margin: auto auto;'>";
echo "<tr><th>Name</th><th>Description</th><th>Model Scale</th><th># In Stock</th><th>Price</th></tr>";
while($row = mysql_fetch_array($result_scale)){
$name = $row['productName'];
$scale = $row['productScale'];
$desc = $row['productDescription'];
$quantity = $row['quantityInStock'];
$price = $row['buyPrice'];
// Now for each looped row
echo "<tr><td style='width: 200px;'>".$name."</td><td style='width: 600px;'>".$desc."</td><td style='width: 100px;'>".$scale."</td><td style='width: 100px;'>".$quantity."</td><td style='width: 100px;'>".$price."</td></tr>";
} // End our scale while loop
echo "</table><br /><br /></div>";
} // End our master loop

Oh, and don’t forget to close your mysql connection in your footer or wherever you finish up using it.

mysql_close();