Display Data From Database Into HTML Table Using PHP
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
- Create a Database
- Create a Database Table
- Dumping Data into Table
- Create a Database Connection Page
- 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>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
I’ve struggled with PHP-MySql for ages, the tutorials and demo have really helped me, thanks for the great demos.
Dear Michael, thanks for stopping by. I am glad that you found my tutorials helpful.
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’?