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

There are few methods using which you can use fetch data from database in PHP and display it into HTML table. In this tutorial, we have explained the following method to fetch data from database in PHP and display it into an HTML table.

Basic Program using Loop
Using Ajax
Using Data Table

1) Basic Program using Loop

<?php
$host = "127.0.0.1"; //IP of your database
$userName = "root"; //Username for database login
$userPass = ""; //Password associated with the username
$database = "example-database"; //Your database name

$connectQuery = mysqli_connect($host,$userName,$userPass,$database);

if(mysqli_connect_errno()){
    echo mysqli_connect_error();
    exit();
}else{
    $selectQuery = "SELECT * FROM `tbl_users` ORDER BY `user_id` ASC";
    $result = mysqli_query($connectQuery,$selectQuery);
    if(mysqli_num_rows($result) > 0){
    }else{
        $msg = "No Record found";
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>HTML and PHP code</title>
</head>
<body>
    <h1>Display user list using HTML and PHP</h1>
    <?=$msg;?>
    <table border="1px" style="width:600px; line-height:40px;">
        <thead>
            <tr>
                <th>Name</th>
                <th>Email</th>
                <th>Status</th>
                <th>Registrating Date</th>
            </tr>
        </thead>
        <tbody>
            <?php
                while($row = mysqli_fetch_assoc($result)){?>
                <tr>
                    <td><?php echo $row['user_firstName'].$row['user_lastName']; ?></td>
                    <td><?php echo $row['user_email']; ?></td>
                    <td><?php if($row['user_status'] == 1){
                        echo "Active";
                    }else{
                        echo "Deactive";
                    } ?></td>
                    <td><?php echo $row['user_registrationDate']; ?></td>
                <tr>
            <?}?>
        </tbody>
    </table>
</body>
</html>

Output

Fetch Data From Database in PHP and Display in HTML Table

2) Using Ajax

HTML File Code

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>HTML and PHP code</title>
</head>
<body>
    <h1>Display user list using HTML and PHP</h1>
    <?=$msg;?>
    <table border="1px" style="width:600px; line-height:40px;">
        <thead>
            <tr>
                <th>Name</th>
                <th>Email</th>
                <th>Registrating Date</th>
            </tr>
        </thead>
        <tbody id="tableBody">
            
        </tbody>
    </table>
</body>
</html>

<script src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>

<script>
$( document ).ready(function() {
    $.ajax({
        url: 'fetch.php',
        mothod: 'post',
        dataType: 'json',
        success:function(data){
            let string = '';
            $.each(data, function(key, value){
                string += `<tr>
                <td>${value['user_firstName']} ${value['user_lastName']}</td>
                <td>${value['user_email']}</td>
                <td>${value['user_registrationDate']}</td>
                </tr>`;
            });
            $('#tableBody').append(string);
        },
        error:{

        }
    });
});
</script>

PHP File Code

<?php
$host = "127.0.0.1"; //IP of your database
$userName = "root"; //Username for database login
$userPass = ""; //Password associated with the username
$database = "example-database"; //Your database name

$connectQuery = mysqli_connect($host,$userName,$userPass,$database);

if(mysqli_connect_errno()){
    echo mysqli_connect_error();
    exit();
}else{
    $selectQuery = "SELECT * FROM `tbl_users` ORDER BY `user_id` ASC";
    $result = mysqli_query($connectQuery,$selectQuery);
    if(mysqli_num_rows($result) > 0){
        $result_array = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($result_array, $row);
        }

    }

    echo json_encode($result_array);

}
?>

Output

Fetch Data From Database in PHP and Display in HTML Table

3) Using Data Table

HTML File Code

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>HTML and PHP code</title>
    <style>
        table, td, th {
            border: 1px solid #ddd;
            text-align: left;
        }

        table {
            border-collapse: collapse;
            width: 100%;
        }

        th, td {
            padding: 15px;
        }
    </style>
</head>

<body>
    <h1>Display user list using HTML and PHP</h1>
    <?= $msg; ?>
    <table id="my-example">
        <thead>
            <tr>
                <th>Name</th>
                <th>Email</th>
                <th>Registrating Date</th>
            </tr>
        </thead>
        <tbody>

        </tbody>
    </table>
</body>

</html>

<script src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>
<link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>

<script>
    $(document).ready(function() {
        $('#my-example').dataTable({
            "bProcessing": true,
            "sAjaxSource": "fetch.php",
            "aoColumns": [{
                    mData: 'user_firstName'
                },
                {
                    mData: 'user_email'
                },
                {
                    mData: 'user_registrationDate'
                }
            ]
        });
    });
</script>

PHP File Code

<?php
$host = "127.0.0.1"; //IP of your database
$userName = "root"; //Username for database login
$userPass = ""; //Password associated with the username
$database = "example-database"; //Your database name

$connectQuery = mysqli_connect($host,$userName,$userPass,$database);

if(mysqli_connect_errno()){
    echo mysqli_connect_error();
    exit();
}else{
    $selectQuery = "SELECT * FROM `tbl_users` ORDER BY `user_id` ASC";
    $result = mysqli_query($connectQuery,$selectQuery);
    if(mysqli_num_rows($result) > 0){
        $result_array = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($result_array, $row);
        }

    }

    $results = ["sEcho" => 1,
        	"iTotalRecords" => count($result_array),
        	"iTotalDisplayRecords" => count($result_array),
        	"aaData" => $result_array ];

    echo json_encode($results);

}
?>

Output

Fetch Data From Database in PHP and Display in HTML Table