Printing a MySQL table to a dynamic HTML table with PHP

Have you every wanted to print out all the data in a MySQL table to a clean looking dynamic HTML table? It is a pretty simple process. In this tutorial we will create a function that can be reused anywhere in your site to dynamicly print the contents of a MySQL table in a clean looking HTML table.

Here is the breakdown:

Construct a query to run on the database.
Run the query and store the result.
Find the number of fields in the returned table.
Construct the html table with the provided attributes.

Here is how to do it step by step:

<?php
include("<---path to MySql connection file--->");
$global_dbh = mysql_connect($hostname, $username, $password)
or die("Could not connect to database");
mysql_select_db($db, $global_dbh)
or die("Could not select database");

This code takes varibles stored in an external file, connects to the database, then selects the database.

function display_db_query($query_string, $connection,
$header_bool, $table_params)
{
// perform the database query
$result_id = mysql_query($query_string, $connection)
or die("display_db_query:" . mysql_error());
// find out the number of columns in result
$column_count = mysql_num_fields($result_id)
or die("display_db_query:" . mysql_error());
// Here the table attributes from the $table_params variable are added
print("<TABLE $table_params >\n");
// optionally print a bold header at top of table
if ($header_bool)
{
print("<TR>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
{
$field_name =
mysql_field_name($result_id, $column_num);
print("<TH>$field_name</TH>");
}
print("</TR>\n");
}
// print the body of the table
while ($row = mysql_fetch_row($result_id))
{
print("<TR ALIGN=LEFT VALIGN=TOP>");
for ($column_num = 0;
$column_num < $column_count;
$column_num++)
{
print("<TD>$row[$column_num]</TD>\n");
}
print("</TR>\n");
}
print("</TABLE>\n"); }

This is the first of two functions needed to print the table. The next fuction simpily passes the variables to the first function.

function display_db_table($tablename, $connection,
$header_bool, $table_params)
{
$query_string = "SELECT * FROM $tablename";
display_db_query($query_string, $connection,
$header_bool, $table_params);
}
?>

Next comes the actual HTML of the page (where the functions will be called).

<HTML><HEAD><TITLE>Displaying a MySQL table</TITLE></HEAD>
<BODY>
<TABLE><TR><TD>
<?php
//In this example the table name to be displayed is  static, but it could be taken from a form
$table = "table1";

display_db_table($table, $global_dbh,
TRUE, "border='2'");
?>
</TD></TR></TABLE></BODY></HTML>

Hopefully you've learned from this tutorial and will find this code usefull. If you have any questions/comments feel free to post. A working example can be found here.

For your convenience, here is the full file (great for being used in an 'include()' statement at the top of your pages):

<?php
include("<---path to MySql connection file--->");
$global_dbh = mysql_connect($hostname, $username, $password)
or die("Could not connect to database");
mysql_select_db($db, $global_dbh)
or die("Could not select database");
function display_db_query($query_string, $connection, $header_bool, $table_params) {
	// perform the database query
	$result_id = mysql_query($query_string, $connection)
	or die("display_db_query:" . mysql_error());
	// find out the number of columns in result
	$column_count = mysql_num_fields($result_id)
	or die("display_db_query:" . mysql_error());
	// Here the table attributes from the $table_params variable are added
	print("<TABLE $table_params >\n");
	// optionally print a bold header at top of table
	if($header_bool) {
		print("<TR>");
		for($column_num = 0; $column_num < $column_count; $column_num++) {
			$field_name = mysql_field_name($result_id, $column_num);
			print("<TH>$field_name</TH>");
		}
		print("</TR>\n");
	}
	// print the body of the table
	while($row = mysql_fetch_row($result_id)) {
		print("<TR ALIGN=LEFT VALIGN=TOP>");
		for($column_num = 0; $column_num < $column_count; $column_num++) {
			print("<TD>$row[$column_num]</TD>\n");
		}
		print("</TR>\n");
	}
	print("</TABLE>\n"); 
}

function display_db_table($tablename, $connection, $header_bool, $table_params) {
	$query_string = "SELECT * FROM $tablename";
	display_db_query($query_string, $connection,
	$header_bool, $table_params);
}
?>
<HTML><HEAD><TITLE>Displaying a MySQL table</TITLE></HEAD>
<BODY>
<TABLE><TR><TD>
<?php
//In this example the table name to be displayed is  static, but it could be taken from a form
$table = "table1";

display_db_table($table, $global_dbh,
TRUE, "border='2'");
?>
</TD></TR></TABLE></BODY></HTML>

Happy coding!
~Syntax-Error

Find this article at:
http://scriptplayground.com/tutorials/php/Printing-a-MySQL-table-to-a-dynamic-HTML-table-with-PHP/