How to Fetch Data From Database Using Ajax in PHP

Fetch and display data from database in php + MySQL + Ajax; In this tutorial, i am going to show you how to fetch data from database in PHP + MySQL and display in table using Ajax.

Using jquery ajax and php to fetch data from a mysql database, i will create simple list and display to customers. Then add the Button View button to the list. When click on view button, call ajax and get data from database and display in html.

PHP Ajax Jquery Get Data from MySQL Database

Follow the below given steps to get data from database in PHP and display in html using Ajax:

Step 1 – Create PHP Project
Step 2 – Create Database Table And Connect App to DB
Step 3 – Create List in HTML Table
Step 4 – Use Ajax Get Data from Database
Step 5 – Fetch Data From Database

Step 1 – Create PHP Project

In step 1, Navigate to your local web server directory. And inside this directory, create one directory. 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 2 – Create Database Table And Connect App to DB

In step 2, you need to create database and table. So run the following sql query to create database and table:

CREATE DATABASE my_db;

CREATE TABLE `customers` (
  `id` int(10) UNSIGNED NOT NULL,
  `fname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

So, now create mydbCon 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

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

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

Step 3 – Create List in HTML Table

In step 3, create a php file that named index.php. This file will display PHP simple customers list. Now, you need to add the following simple code into index.php file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>PHP Code to Fetch All 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" >
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
</head>
<body>

<div class="container mt-2">
        <div class="page-header">
          <h2>Customers List</h2>
      </div>
    <div class="row">
        <div class="col-md-8">
            <table class="table">
              <thead>
                <tr>
                  <th scope="col">#</th>
                  <th scope="col">First</th>
                  <th scope="col">Last</th>
                  <th scope="col">Email</th>
                  <th scope="col">Action</th>
                </tr>
              </thead>
              <tbody>
                <?php

                include 'mydbCon.php';

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

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

                ?>

                <?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>
                    <td> 
                      <a href="javascript:void(0)" class="btn btn-primary view" data-id="<?php echo $array[0];?>">View</a>
                </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 class="col-md-4">
          <span id="fname"></span><br>
          <span id="lname"></span><br>
          <span id="email"></span><br>
        </div>
    </div>        
</div>

</body>
</html>

Recommended:-Form validation using jQuery in PHP

Step 4 – Use Ajax To Get Data From Database

In step 4, implement ajax jQuery code to fetch data from MySQL database and display in html and add the following code into index.php file:

<script type="text/javascript">
 $(document).ready(function($){
 
     $('body').on('click', '.view', function () {

        var id = $(this).data('id');
         
        // ajax
        $.ajax({
            type:"POST",
            url: "ajax-fetch-record.php",
            data: { id: id },
            dataType: 'json',
            success: function(res){

              $('#fname').html(res.fname);
              $('#lname').html(res.lname);
              $('#email').html(res.email);
           }
        });

 
    });

});
</script>

Step 5 – Fetch Data From Database

In step 5, create new file that named ajax-fetch-record.php file. Because this is used to fetch data from database and send data to index.php file:

<?php
    include "mydbCon.php";
 
    $id = $_POST['id'];

    $query="SELECT * from customers WHERE id = '" . $id . "'";

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

    $cust = mysqli_fetch_array($result);

    if($cust) {

     echo json_encode($cust);

    } else {

     echo "Error: " . $sql . "" . mysqli_error($dbCon);

    }
 
?>

Conclusion

To Get/Fetch and display data from database in php + MySQL + Ajax; In this tutorial, You have learned how to fetch data from database in PHP + MySQL and display in table using Ajax.