How to Return MySQL Results to a Table
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:
Popularity: unranked [?]
Related Posts
on April 19th, 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.
on April 28th, 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 }
on April 28th, 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 }
*/
on April 28th, 2009 at 5:42 am
Sorry My whole code does not appear….
on August 6th, 2009 at 5:05 am
Hi,Thanks for sharing this nice info related good and important topic to Return MySQL Results to a Table.
on August 26th, 2009 at 7:39 pm
The code looks quite a mess..