How to Return MySQL Results to a Table
- Categories: PHP Tutorials,Web Development Tutorials
- Author: John Ward
- Comments: 17 responses
- Tags: html, mysql, php, tables
-
This question has been asked several times lately and I wanted to clear this up. This is a very easy concept once you get it down. In order to display the results from a MySQL query into a table will will store the results in an array and then echo out each row in a while loop.
First off. I am going to use a table that I created in the past tutorials on this site.
The table contains the following fields:
ID – auto generated integer
FName – First Name, varchar
LName – Last Name, varchar
PHON – Phone number, varchar
Create the table and populate some test data if you haven’t done so already. If you don’t know how to do this please visit some of our other MySQL tutorials.
Create your database connection
<?php //create a database connection mysql_connect("localhost","your username","password") or die("Error: ".mysqlerror()); //select your database mysql_select_db("your db"); ?>
Next we need to setup the table. We will add the column headings.
<?php //create a database connection mysql_connect("localhost","your username","password") or die("Error: ".mysqlerror()); //select your database mysql_select_db("your db"); ?> <html> <body> <table border=1> <tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th>Phone Number</th> </tr>
As you can see above we ended our PHP code and create an HTML table. The TR tage is used to defined a table row and the TH tag is used for Table Headings.
In php you can “break” out of code and execute HTML and then continue writing php code. Do do this you simply end your php code with then start a new
So the next setup will be to add the php code to select our items from the database.
<?php
//create a database connection
mysql_connect("localhost","your username","password") or die("Error: ".mysqlerror());
//select your database
mysql_select_db("your db");
?>
<html>
<body>
<table border=1>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Phone Number</th>
</tr>
<?php
//create the query
$query = mysql_query("select * from `TestTable`");
//return the array and loop through each row
while ($row = mysql_fetch_array($query)){
$id = $row['ID'];
$fname = $row['FName'];
$lname = $row['LName'];
$phone = $row['PHON'];
?>
Above you will see that we executed a query and returned the results as an array. We will loop through each record and echo the results in the table. Take notice that I DID NOT CLOSE the if statment here on purpose.
Now to we will add the HTML for the rows that are returned. We will use php and echo the results.
<?php
//create a database connection
mysql_connect("localhost","your username","password") or die("Error: ".mysqlerror());
//select your database
mysql_select_db("your db");
?>
<html>
<body>
<table border=1>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Phone Number</th>
</tr>
<?php
//create the query
$query = mysql_query("select * from `TestTable`");
//return the array and loop through each row
while ($row = mysql_fetch_array($query)){
$id = $row['ID'];
$fname = $row['FName'];
$lname = $row['LName'];
$phone = $row['PHON'];
?>
<tr>
<th><?php echo $id;?></th>
<th><?php echo $fname;?></th>
<th><?php echo $lname;?></th>
<th><?php echo $phone;?></th>
</tr>
Now all we have to do is end the if statement and close all of our open HTML tags.
<?php
//create a database connection
mysql_connect("localhost","your username","password") or die("Error: ".mysqlerror());
//select your database
mysql_select_db("your db");
?>
<html>
<body>
<table border=1>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Phone Number</th>
</tr>
<?php
//create the query
$query = mysql_query("select * from `TestTable`");
//return the array and loop through each row
while ($row = mysql_fetch_array($query)){
$id = $row['ID'];
$fname = $row['FName'];
$lname = $row['LName'];
$phone = $row['PHON'];
?>
<tr>
<th><?php echo $id;?></th>
<th><?php echo $fname;?></th>
<th><?php echo $lname;?></th>
<th><?php echo $phone;?></th>
</tr>
<?php } //this ends the if?>
</table>
</html>
If you do it right you will get something like this:
Ryan on April 19, 2009 at 7:15 am
This is great to get the loop started however each entry is repeated.
Really easy to get started… just need to work out how to stop the repeatition of the data.
[Reply]
Red Servo on April 28, 2009 at 5:38 am
This is my code… What is wrong with this? I am using Linux Mandriva and phpMyAdmin.. Please Analyse this:
Name
Age
Date
Comment
<?php }
[Reply]
Red Servo on April 28, 2009 at 5:39 am
This is my code… What is wrong with this? I am using Linux Mandriva and phpMyAdmin.. Please Analyse this:
/*
Name
Age
Date
Comment
<?php }
*/
[Reply]
Red Servo on April 28, 2009 at 5:42 am
Sorry My whole code does not appear….
[Reply]
football on August 6, 2009 at 5:05 am
Hi,Thanks for sharing this nice info related good and important topic to Return MySQL Results to a Table.
[Reply]
generic on August 26, 2009 at 7:39 pm
The code looks quite a mess..
[Reply]
Cetvn on January 24, 2010 at 1:55 am
I think this code have missing line.
[Reply]
Söve on February 8, 2010 at 9:31 am
I agree generic.It looks quite a mess.
[Reply]
John Ward on February 16, 2010 at 9:23 pm
I think the plugin broke this code
[Reply]
John Ward on February 16, 2010 at 9:31 pm
Code should be correct now.
[Reply]
yudi on February 22, 2010 at 1:03 pm
Thanks for the code.
[Reply]
mehmet on March 31, 2010 at 7:58 am
i can insert data in mysql by php but i want add two releation table with same id fore example user will have two table firstli he add information in a table from a form after that he can add any information in second table in different time.how can i use same id in two table andthats id can be unique for thats users?
[Reply]
Superguyent on April 9, 2010 at 4:16 pm
I am getting the following error.
Parse error: syntax error, unexpected ‘;’, expecting T_PAAMAYIM_NEKUDOTAYIM in /home/vpgonlin/public_html/pcf/showdata.php on line 21
Below is my Code can someone help me fix this?
ID
Company
[Reply]
Superguyent on April 9, 2010 at 4:17 pm
//
//
//
[Reply]
Superguyent on April 9, 2010 at 4:18 pm
Anyway I could not post my code, but I have it exactly as the example code above. Line 21 I am getting an error.
[Reply]
John Ward Reply:
April 9th, 2010 at 7:52 pm
There us a problem with the code since we upgraded. For some reason the quotes aren’t displaying properly. It would also help if your error was I English but my guess is you forgot a semicolon or have a syntax error
[Reply]
chris on May 21, 2010 at 3:20 am
Thanks the code works. there is one problem. when the user decreases the size of the page the table jumbles up to continue to fite all of the info inside. Is there any overflow code that can be used to fix the problem?
[Reply]