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.
The Flash and PHP Bible has a forum for quick support.
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.
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 |
|
cgrosso Sun Jan 11, 2009 6:47 pm
How may i print titles too?
Thanks for the quick response |
|
mkeefe Mon Jan 12, 2009 8:26 am
@cgrosso - In order to print table titles use the <th> tag wrapped around the title string you would like to use.
|
|
Tom Wed Feb 25, 2009 4:27 pm
Hi Matt
Great article. Could you please give some directions on how to make the html table editable (add/delete/edit records), and then save the changes back into that database? Thanks, Tom |
|
mkeefe Wed Mar 4, 2009 9:59 am
@Tom - The simplest way to add the option of editing/deleting would be to use jQuery or one of the other Javascript frameworks and create a PHP back end to handle the processing.
I have an "admin" panel tutorial in the list soon so you may actually see just that. |
|
Guest Tue Apr 28, 2009 11:20 am
Muchas gracias... esto si es una buena explicacion...
lo felicito. |
|
peter Fri May 15, 2009 5:00 pm
how do I filter the data, to only show rows with specific data?
I tried placing an if statement around the print command, but with no luck. |
|
mkeefe Mon May 25, 2009 4:21 pm
@peter - I would use the
WHERE clause in the MySQL select statement.
|
|
Jerrod Long Fri Jun 26, 2009 5:09 pm
@tom - I would say that you could also include in your print statements the HTML form tags with submit functions that called the drop table or whatever command based upon the id of the record you wanted to edit/delete.
|
|
Robert Clemens Fri Jul 10, 2009 7:03 am
thanks a lot! it worked right away
|
|
Joaquim Mon Aug 3, 2009 10:49 pm
MAN! Thanks a lot!
I've been searching this for days! You rock! :-) |
|
Zamshed Farhan Mon Aug 10, 2009 10:50 pm
Many many thanks for such a well-structured and comprehensive tutorial, and it covers exactly what I was looking for.
|
|
Forsooth Fri Oct 9, 2009 3:46 pm
Glad I found this! Thanks very much!!
|
|
shahbaz Wed Oct 14, 2009 12:45 am
thanks for the tut.
But can u tell me how to add a check box in the last column of the table and binding it to the row that is generated. thanks again |
|
McBob Fri Nov 20, 2009 3:30 pm
Excellent example
|
|
mac Wed Dec 2, 2009 9:43 am
hi there..
the link "working example can be found here" takes you to some other non related site.. |
|
mkeefe Wed Jan 6, 2010 5:21 pm
@mac - Thanks for bringing that to our attention. It seems the sample site has been taken offline. I will try to set up a sample hosted on Scriptplayground.
|
|
iSoftech Wed Feb 10, 2010 5:38 am
Hi, First of all I need to Thank you for this code.
Can anyone help me to create a Dynamic Table with Colspan and Rowspan ?... |
|
juankaos Thu Mar 4, 2010 3:31 pm
thank you very much, exactly what i was looking for
|
|
Nicolai Dutka Sat Mar 6, 2010 8:56 pm
How would I make the column headings/titles 'clickable' so that the table is 'ORDER BY' that column? Also, I'd like to click a second time to change the order from ascending to descending or vice versa.
Example: A table that has columns: Name, Age, Weight The table lists out by 'Name', descending, by default. When I click on 'Name', the order changes from descending to ascending. When I click on 'Age', the table is re-ordered by Age, descending. Any ideas? |
|
madMax Sun Mar 7, 2010 2:31 pm
Great tutorial, man! Thank you so much! :-)
|
|
Nicolai Dutka Wed Mar 17, 2010 10:25 am
Follow this thread to learn how to take this code to the next level, making all the columns 'sortable' via clicking the column names:
http://www.ozzu.com/programming-forum/clickable-sortable-table-headers-t102557.html |
|
Alex Fri Apr 16, 2010 10:33 pm
I am confused.. Do I put this code in a single php file? Do I create another php file with all my variables? What exactly are the variables?
$hostname = ""; $username = ""; $password = ""; $db = ""; Where is the variable where I specify the table? Am I missing any other variables? Could I also include this variables at the top of all the code. Thank you!! |
|
JTC Thu Apr 22, 2010 4:27 am
If you want something editable/sortable etc.
then have a look at "phpMyEdit" (google it). This script is great to make a printable html. |
|
Mike Sat Jul 10, 2010 9:38 am
Your working example link does not show the output, can you update it? Please.
|
©2004 - 2010 scriptplayground | Privacy Policy | Legal
Validate Site: XHTML CSS | Designed by: Matthew Keefe of mkeefeDESIGN