How to Fetch Data From Database in PHP and Display HTML Table

To fetch and display data from MySQL database in php; Through this tutorial, i will completely guide you on how to fetch/retrieve data from mysql database in php and display in bootstrap html table.

Note that – You must have installed XAMPP server on your window, ubuntu or Mac machine. As well as any text editor installed, which can be used to write code in your php files.

Fetch Data From Database in PHP and Display in HTML Table

Let’s use the following steps to retrieve data from mysql database and display in html table in PHP using the below given code:

  • Step 1 – Start Apache Web Server
  • Step 2 – Create PHP Project
  • Step 3 – Execute SQL query to Create Table
  • Step 4 – Create phpmyadmin MySQL Database Connection File
  • Step 5 – Create Fetch Data PHP File From Database
  • Step 6 – Create Html Table To Display Retrieve Data From MySQL DB
  • Step 7 – Open Browser And Test This Project

Step 1 – Start Apache Web Server

Now, you need to start your apache web server. And as well as start Apache and mysql web server. You can see in the image below how to start apache and mysql server:

Step 2 – Create PHP Project

In step 2, navigate to your xampp/htdocs/ directory. And inside xampp/htdocs/ directory, create one folder. And you can name this folder anything.

Here, I will “demo” the name of this folder. Then open this folder in any text editor (i will use sublime text editor).

Step 3 – Execute SQL query to Create Table

In step 3, open your browser and type http://localhost/phpmyadmin. Then create database and table by running the following sql query:

CREATE DATABASE my_db;


CREATE TABLE `customers` (
`custId` INT(11) NOT NULL AUTO_INCREMENT,
`fname` VARCHAR(255) NULL DEFAULT NULL,
`lname` VARCHAR(255) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`created` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`custId`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

INSERT INTO `customers` (`custId`, `fname`, `lname`, `email`, `created`) VALUES (NULL, 'Tiago', 'Sam', '[email protected]', NULL), (NULL, 'Anil', 'Kumar', '[email protected]', NULL);

Step 4 – Create phpmyadmin MySQL Database Connection File

In step 4, create a php file that named mydbCon.php. Which is used to connect phpmyadmin mysql database to project (demo).

So, now create mydbCon.php file and add the below given code into your file:

<?php

    $hName='localhost'; // host name

    $uName='root';   // database user name

    $password='';   // database password

    $dbName = "my_db"; // database name

    $dbCon = mysqli_connect($hName,$uName,$password,"$dbName");

      if(!$dbCon){
          die('Could not Connect MySql Server:' .mysql_error());
      }
?>

Step 5 – Create Fetch Data PHP File From Database

In step 5, you need to create a php file that named retrieve-data.php. Which is used to retrieve or fetch data from MySQL database table.

So add the following code on retrieve-data.php file:

<?php


include 'mydbCon.php';

$query="select * from customers limit 50"; // Fetch all the data from the table customers

$result=mysqli_query($dbCon,$query);

?>

Step 6 – Create Html Table To Display Retrieve Data From MySQL DB

In step 6, you need create a php file that named index.php. Which is used to fetch all the data from the MySQL database table.

Note that, you need to include retrieve-data.php file on index.php file. Don’t worry i have already include this file on index.php file.

So, you can add this code in your index.php file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>PHP Code to Retrieve Data from MySQL Database and Display in html Table</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>

<div class="container mt-2">
    <div class="row">
        <div class="col-md-12">
            <div class="page-header">
                <h2>Fetch Data From Database in PHP and Display in HTML Table</h2>
            </div>

            <table class="table">
              <thead>
                <tr>
                  <th scope="col">#</th>
                  <th scope="col">First</th>
                  <th scope="col">Last</th>
                  <th scope="col">Email</th>
                </tr>
              </thead>
              <tbody>
                <?php include 'retrieve-data.php'; ?>

                <?php if ($result->num_rows > 0): ?>

                <?php while($array=mysqli_fetch_row($result)): ?>

                <tr>
                    <th scope="row"><?php echo $array[0];?></th>
                    <td><?php echo $array[1];?></td>
                    <td><?php echo $array[2];?></td>
                    <td><?php echo $array[3];?></td>
                </tr>

                <?php endwhile; ?>

                <?php else: ?>
                <tr>
                   <td colspan="3" rowspan="1" headers="">No Data Found</td>
                </tr>
                <?php endif; ?>

                <?php mysqli_free_result($result); ?>

              </tbody>
            </table>
        </div>
    </div>        
</div>

</body>
</html>

Step 7 – Open Browser And Test This Project

Finally, you need to open your browser and type http://localhost/demo this run project on local machine.

This project will be looks like shown in below image:

how to retrieve data from database in php and display in html table

Thanks for reading this post.

Recommended PHP Tutorials

Leave a Comment