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.
READ MORE »