Display Data From Database Into HTML Table Using PHP


Demo Download

Today I will share how to display data from MySQL database into HTML table using PHP. I will also show that how to store values from database table into HTML array, and how to handle that array in PHP. For this purpose first I will create a database with name allphptricks  and then create a table in database with name sports  and dump dummy data into it.

Steps to Display Data From Database Into HTML Table Using PHP

  1. Create a Database
  2. Create a Database Table
  3. Dumping Data into Table
  4. Create a Database Connection Page
  5. Create a Main Index Page

1. Create a Database

Execute the following query in your MySQL query.

CREATE DATABASE allphptricks;

2. Create a Database Table

Run the following query in your above database.

CREATE TABLE IF NOT EXISTS `sports` (
  `sport_id` int(10) NOT NULL AUTO_INCREMENT,
  `sport_name` varchar(100) NOT NULL,
  PRIMARY KEY (`sport_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

3. Dumping Data into Table

Now I will insert sample data in our table, following query is dumping data into table.

INSERT INTO `sports` (`sport_id`, `sport_name`) VALUES
(1, 'Cricket'),
(2, 'Football'),
(3, 'Basketball'),
(4, 'Hockey'),
(5, 'Golf'),
(6, 'Badminton'),
(7, 'Boxing'),
(8, 'Cycling BMX'),
(9, 'Canoe Sprint');

For your ease I have also attached an sport.sql file in this tutorial download, you can import that file if you do not want to create table and dumping data.

4. Create a Database Connection Page

Create a database connection page with name db.php and copy the following code in it.

<?php
// Enter your Host, username, password, database below.
// I left password empty because i do not set password on localhost.
$con = mysqli_connect("localhost","root","","allphptricks");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
?>

5. Create a Main Index Page

Now create a main index.php  page which contain the actual script of PHP which will show data from database into HTML table and also it will store all values in HTML array and on form submission, it will catch all selected values and print them on web page, you can also store it in your database.

Now copy the below code in your index.php  page right after start of <body>  tag

HTML Form & PHP Loop Script

<form name="form" method="post" action="">
<label><strong>Select Sports:</strong></label><br />
<table border="0" width="60%">
<tr>
<?php
$count = 0;
$query = mysqli_query($con,"SELECT * FROM sports");
foreach($query as $row){
	$count++;
?>
<td width="3%">
<input type="checkbox" name="sports[]" 
value="<?php echo $row["sport_id"]; ?>">
</td>
<td width="30%">
<?php echo $row["sport_name"]; ?>
</td>
<?php
if($count == 3) { // three items in a row
        echo '</tr><tr>';
        $count = 0;
    }
} ?>
</tr>
</table>
<input type="submit" name="submit" value="Submit">
</form>

<br />
<?php echo $status; ?>

In the input field name I used sports[]  these square brackets will make it HTML array. The above code will get all sports name from database table and view them in the HTML table form, each row display three sports name. But this will also need database connection, so copy the below code before the start of <html> tag in the page header.

DB Connection & PHP Loop Script of Array

<?php
include('db.php');
$status = '';
if (!empty($_POST['sports'])){
if (is_array($_POST['sports'])) {
$status = "<strong>You selected the below sports:</strong><br />";
foreach($_POST['sports'] as $sport_id){
$query = mysqli_query
    (
    $con,
    "SELECT * FROM sports WHERE `sport_id`='$sport_id'"
    );
$row = mysqli_fetch_assoc($query);
$status .= $row['sport_name'] . "<br />";
   } 
  } 
} 
?>

Above code will not only include database connection file but it will also check if form is submitted so it will check array variable, if it is array then it will start foreach loop to catch all selected values by user and store it in $status  variable which will display result below HTML table.

Little CSS of HTML Table

Add following CSS in your index.php  before closing </head>

<style>
table td {
    border-bottom: 1px solid #f1f1f1;
}
</style>

Demo Download

If you found this tutorial helpful, share it with your friends and developers group.

Facebook Official Page: All PHP Tricks

Twitter Official Page: All PHP Tricks

Article By
Javed Ur Rehman is a passionate blogger and web developer, he loves to share web development tutorials and blogging tips. He usually writes about HTML, CSS, JavaScript, Jquery, Ajax, PHP and MySQL.
  1. I’ve struggled with PHP-MySql for ages, the tutorials and demo have really helped me, thanks for the great demos.

  2. Suppose the table has a url field. How would you fetch and store a url from the table and store it in the ‘status variable’ then display an inmage from the url above the ‘sport_name’?

Leave a Reply

Your email address will not be published. Required fields are marked *