How to Fetch Data From Database and Show the Data in the HTML Table Using PHP

Sarwan Soomro Feb 02, 2024

PHP PHP Database HTML

Create a Database and Table in MySQL
Connect to the MySQL Server in PHP
Show Data in HTML Table Using PHP

How to Fetch Data From Database and Show the Data in the HTML Table Using PHP

This tutorial will teach you the step-by-step process of how to fetch the MySQL table and show records in the HTML using PHP.

Create a Database and Table in MySQL

First, we will create a "demo" database and a "products" table. You can use either PHPMyAdmin MySQL or SQLyog to execute the following SQL query:

MySQL Query:

/*Your SQL queries*/
CREATE DATABASE demo; /*phpmyadmin MySQL Database Query*/
/*or*/
CREATE DATABASE demo; /*SQLyog Database Query*/
USE demo;

/*Table structure*/
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `Manufacturer` char(60) DEFAULT NULL,
  `Module` char(60) DEFAULT NULL,
  `Series` char(60) DEFAULT NULL,
  `MPN` char(60) DEFAULT NULL,
  `Function` char(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*Data for the table*/
insert  into `products`(`id`,`Manufacturer`,`Module`,`Series`,`MPN`,`Function`) values
(1,'Microsoft','Operation System','A','1263187','OS'),
(2,'Amazon','Web Services','B','3473747','Web'),
(3,'Rockwell Automation','Electronic Modules','C','9854747','Machine Control'),
(4,'Facebook','Social Connectivity','D','1271517','Social'),
(5,'Google','Search Engine','E','6372673','Search');

To import these records, you can copy this query and run it directly in PHPMyAdmin MySQL or SQLyog.

The table "products" contains 5 table rows and columns as follows:

Product ID
Product Manufacturer
Module Type
Product Series
Product Function

After creating our database, we’ll connect our database.php file with the MySQL server in PHP.

Connect to the MySQL Server in PHP

Let’s understand a few key MySQL functions used in PHP.

define() - define localhost information.
mysqli_connect() - connect to the MySQL by passing parameters from the define() function.
die(mysqli_connect_error()) - shows error in the occurrence of database failure and the db dies.

Code Snippet (database.php):

<?php
define("server", "localhost");
define("user", "root");
define("password", "");
define("database", "demo");
//mysql_connect(); parameters
$connect = mysqli_connect(server, user, password, database);
//run a simple condition to check your connection
if (!$connect)
{
    die("You DB connection has been failed!: " . mysqli_connect_error());
}
$connection = "You have successfully connected to the mysql database";
//echo $connection;
?>

Output:

You have successfully connected to the MySQL database.

Now that we are connected to the MySQL server let us retrieve the data in the PHP script.

Show Data in HTML Table Using PHP

We will include database.php using the require_once() function. Then a while loop will dynamically create data from the mysql_fetch_array() properties.

HTML (Index.php):

<!DOCTYPE html>
<body>
   <head>
      <title> Fetch data from the database in show it into a HTML table dynamically</title>
      <link rel="stylesheet" href="style.css">
   </head>
 <form action="index.php" method="post" align="center">
<input type="submit" name="fetch" value="FETCH DATA" />
</form>

The styling style.css and HTML are just for the front-end matter we have incorporated within our index.php file.

PHP Script (Index.php):

 <?php
//fetch connection details from database.php file using require_once(); function
require_once ('database.php');
//check if it work!
echo $connection; //from database.php file
if (isset($_POST['fetch']))
{
    //mysql_query() performs a single query to the currently active database on the server that is associated with the specified link identifier
    $response = mysqli_query($connect, 'SELECT * FROM products');
    echo "<table border='2' align='center'>
<H2 align='center'> Products Table </h2>
<tr>
<th>Product ID</th>
<th>Product Manufacturer</th>
<th>Product Type</th>
<th>Product Series</th>
<th>MPN</th>
<th>Product Function</th>
</tr>";
    while ($fetch = mysqli_fetch_array($response))
    {
        echo "<tr>";
        echo "<td>" . $fetch['id'] . "</td>";
        echo "<td>" . $fetch['Manufacturer'] . "</td>";
        echo "<td>" . $fetch['Module'] . "</td>";
        echo "<td>" . $fetch['Series'] . "</td>";
        echo "<td>" . $fetch['MPN'] . "</td>";
        echo "<td>" . $fetch['Function'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";

    mysqli_close($connect);
}
?>

The isset($_POST['fetch']) function is triggered upon the form submission. Then we used mysql_query('Your query') to select all records from the products table.

We stored them in the $response variable. Afterward, we used a while loop to generate a table until the mysql_fetch_array() has finished fetching records in the form of the array index.

The $fetch['array_index'] targets the previously stored array index that mysql_query affected successfully.

Output:

Fetch SQL database and show it in HTML table using PHP

Author: Sarwan Soomro

Sarwan Soomro is a freelance software engineer and an expert technical writer who loves writing and coding. He has 5 years of web development and 3 years of professional writing experience, and an MSs in computer science. In addition, he has numerous professional qualifications in the cloud, database, desktop, and online technologies. And has developed multi-technology programming guides for beginners and published many tech articles.