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.
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.
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
Follow Scriptplayground on Twitter (@scriptplay)
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.
|
|
Rex Sat Nov 13, 2010 5:23 pm
I've used the code on my site, (http://dollapal.com/offerlist.php) trying to display a list of offers, with links to each. I actually have two questions:
Is there a way to EXCLUDE columns of the table? I would rather not show the 'id' or 'points' columns. I would also like use the title field as a hyperlink to the URL listed in the URL field, instead of simply printing the URL. Are there any simple ways to do this? |
|
mkeefe Mon Nov 15, 2010 7:12 pm
@Alex - You can include a separate file, or just list them at the top of the one file, your choice.
@Mike - The original author must have moved hosts, this is why all future tutorials on Scriptplayground are hosted in-house. I will have the script posted on our servers soon, thanks. @Rex - In place of "*" you would list out the columns, such as "column1, column2, column3". For the second part you would just wrap the result in an A href tag. |
|
mkeefe Mon Nov 15, 2010 7:53 pm
@Mike - The example code has been uploaded to the Scriptplayground server. Sorry for the confusion!
|
|
cj Tue Feb 1, 2011 9:40 pm
this was exactly what i was looking for, thanks for keeping this site up and running
|
|
celina gomz Tue Feb 15, 2011 10:26 pm
Nice.... I like it.......... but i cant understand it..... only good looking code....
|
|
Komal & minal Tue Feb 15, 2011 10:29 pm
Nice... It is little bit helpful to us....
|
|
Corina Sat Apr 2, 2011 5:18 pm
How can I put the whole $row in one <td>. And the next $row has to come in the next column or <td>.
I hope I'm explaining it wel because englisch is not my first Language ;-) |
|
Corina Sat Apr 2, 2011 5:22 pm
Sorry I used td html tags in my question en so I cut my message in td's. I can't change de message so that's why I apologize ;-)
|
|
Corina Mon Apr 4, 2011 4:14 am
Is there anyone who can help? How can I put the whole $row in one td? And then de next $row in de next td. 3 koloms in one table row?
|
|
Corina Mon Apr 4, 2011 4:41 am
Like this:
column column column ----------------------------------------------------- row picture | picture | picture compagny | compagny | compagny city | city | city ----------------------------------------------------- row bla | bla | bla |
|
D Fri Apr 15, 2011 2:53 pm
Forgive the ignorant question, but how many items are there in the example code that need to be removed and replaced with information unique to the user's website?
|
|
Hyderabad Mon Apr 18, 2011 7:58 am
Excellent. This should help me with my new php-mysql tracking system for my Packers and movers client. Thanks a lot!
|
|
mkeefe Mon Apr 18, 2011 1:54 pm
The database connection lines and the SQL code is unique to your own website. Other than that it should all work with any setup.
|
|
chris Wed May 11, 2011 4:17 am
im trying to figure out how to paginate this to show only 25 results per page..
|
|
mkeefe Sun May 15, 2011 7:38 pm
@chris - For pagination you would want to limit the resultset and then using a QUERY parameter on the URL just load that set. Finally create the links to load each page.
|
|
Al Hatch Mon Jul 4, 2011 2:55 pm
Each time you use it, you must replace portions of these 4 lines. Replace the 3 $variables with whatever names you chose to use in your connection (php) file. Enter the other two (the path and the table name) directly into the Web page.
include("<---path to MySql connection file--->"); $global_dbh = mysql_connect($hostname, $username, $password) mysql_select_db($db, $global_dbh) [the name of the database] $table = "table1"; |
|
eoaddai Wed Jul 13, 2011 11:37 pm
hi mkeefe, have u posted the jquery editable table assignment you promised yet? eagerly waiting to see that.
|
|
proremmy@gmail.com Tue Aug 16, 2011 3:11 am
the code has failed on line 94 with error
Parse error: parse error, unexpected T_ELSE in D:wampwwwphase2SANajax.php on line 93 any help |
|
Dave Mon Sep 12, 2011 10:48 am
Excellent, this is exactly what I was looking for. I just wish PHPMyAdmin had something like this built-in.
|
|
creazioni siti Thu Nov 3, 2011 3:40 am
Hello can i download it from url? i dont find a link
regards |
|
Adam Tue Dec 13, 2011 11:41 pm
This script is so elegant and just resolved my big problem.
|
|
Jarad Mon Dec 26, 2011 7:34 pm
(I am a newb so bear with me)
When I plug in my values I guess there is someplace where I am missing where the table is defined. When I run my .php page I get: display_db_query:Table 'inventory.table1' doesn't exist. My database name is inventory my table name is weapon. I tweaked the vars to just be inside of the script for now will add to an external file later. Looking at the code I see this area: function display_db_query($query_string, $connection, $header_bool, $table_params) what are the vars for $query_string, %connection, %header_bool, %table_params used for? (I also tried to definate %table_params as my table name that did not work either) Newb help and patience requested please LOL. |
|
Martin Mon Jan 9, 2012 10:56 pm
If I only want to display certain columns in my html table. what can I edit to do that? like say if i have 12 or 13 columns, but I only want to display columns for Name, Email, and Phone Number or something like that.
|
|
Vids Thu Jan 19, 2012 2:49 pm
hi mkeefe, have u posted the jquery editable table assignment you promised yet? I'm looking forward for that post!
|
Loading
©2004 - 2012 scriptplayground | Privacy Policy | Legal
Validate Site: XHTML CSS | Designed by: Matthew Keefe of mkeefeDESIGN