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.
View an Example of this article before you get started.
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 Code:
<?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.
PHP Code:
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.
PHP Code:
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 Code:
<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 Code:
<?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
07-05-2007, 10:12 AM
Printing a MySQL table to a dynamic HTML table with PHP
Linear Mode
