Flash and PHP Bible
The Flash and PHP Bible has been released! The book can be found on Amazon or wherever fine books are sold in your area. This book explains the process of working with PHP in Flash, while creating real world examples that you can actually learn something from.
The Flash and PHP Bible has a dedicated forum for support and comments.
Scriptplayground » tutorials » php » Printing a MySQL table to a dynamic HTML table with PHP
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.
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
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
Print It
Comments: Printing a MySQL table to a dynamic HTML table with PHP
|
Servisis Fri Jun 2, 2006 7:17 pm
Thanks so much for such a well-structured and comprehensive tutorial, and it covers exactly what I was looking for.
|
|
BibleGuard Sat Nov 11, 2006 9:54 am
Really nice:)That was helpful:)
|
|
soft Tue Dec 12, 2006 9:52 pm
can i get a script to Printing a MySQL table to HTML table
|
|
php manual Sat Jan 27, 2007 7:53 am
is there any scripts to do this functions??
|
|
Tayfun Demirbilek Thu Nov 22, 2007 4:29 am
Well done.
|
|
laruem Wed Dec 5, 2007 1:35 pm
This script is exactly what I need for my site, but I am having trouble understanding exactly what variables I need to change to my database infomation. Can someone help me out?
|
|
mkeefe Wed Dec 5, 2007 1:49 pm
Hello,
In order to determine your database information you would need to contact your system admin or host. Matt |
|
laruem Wed Dec 5, 2007 1:54 pm
I know what my database info is. I need to know if I should run this script as is, or what I have to change in order for it to work. Thanks for the quick response.
|
|
mkeefe Wed Dec 5, 2007 4:19 pm
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"); You can either fill in the variables as seen in the mysql_connect function or place those in an external file and include it as shown on line 2 The table name is passed in as the first argument and is statically defined above that, but you could dynamically retrieve that result from an external source or form. Hope that helps, Matt |
|
laruem Thu Dec 6, 2007 9:37 am
I defined the variables in the mysql_connect function and defined the table name. Now it is printing out all the php code starting with \n"); in the print (<TABLE $table_params >\n"); statement. ???
|
|
laruem Thu Dec 6, 2007 10:07 am
Sorry....
after the print function for the Table attributes |
|
mkeefe Thu Dec 6, 2007 10:53 am
I am not 100% sure, but I noticed the author that submitted this tutorial has the contents printed within the function. The correct way to do this would be to create a new variable in the function and replace the print statements with that variable.
Note: After the 1st variable you would use a $varName .= ""; to ensure all the lines of data are captured. Hope that helps, Matt |
©2004 - 2008 scriptplayground | Privacy Policy | Legal
Validate Site: XHTML CSS | Designed by: Matthew Keefe of mkeefeDESIGN