How to Create Jquery Datatable With Jquery Using PHP

admin_img Posted By Bajarangi soft , Posted On 23-09-2020

There are many ways to get your data into DataTables, and if you are working with seriously large databases, you might want to consider using the server-side options that DataTables provides. Basically all of the paging, filtering, sorting etc that DataTables does can be handed off to a server and DataTables is just an events and display module. The example here shows a very simple display of the CSS data but in this instance coming from the server on each draw. Filtering, multi-column sorting etc all work as you would expect.

How to Create Datatable Using Ajax and PHP


1.Create index.php and implement code as below .

<?php
$con = mysqli_connect("localhost", "root", "", "datatablephp");?>

<!DOCTYPE html>
<html>
<head>
    <title>Datatables</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
<br /><br />

<div class="container">
    <h3 align="center">How to Create Datatable Using Ajax and PHP</h3>
    <br />
    <div class="col-md-6" style="display: flex;">
        <lable>Gender:</lable>
        <select class="form-control" name="multi_search_filter"  required>
            <option disabled selected value>Select an option</option>
            <?php
            $sql = 'SELECT DISTINCT gender FROM tbl_employee';
            $res = mysqli_query($con, $sql);
            if ($res):
                while ($row = mysqli_fetch_assoc($res)) {
                    echo '<option  value="'.$row['gender'].'">'.$row['gender'].'</option>';
                }
            endif; ?>
        </select>
    </div>
   <br />
   <br />
</div>
<div class="container">
    <div class="table-responsive">
        <table id="employee_data" class="table table-striped table-bordered">
            <thead>
            <tr>
                <td>Id</td>
                <td>Name</td>
                <td>Address</td>
                <td>Gender</td>
                <td>Designation</td>
                <td>Age</td>
            </tr>
            </thead>
        </table>
    </div>
</div>
</body>
</html>

you can see above code in that we create database connection to fetch data for dropdown so that we can filter the data from dropdown .

2.Now implement jquery to display datatable as below

<script>
    $(document).ready(function(){
        $('#employee_data').DataTable({
            'processing': true,
            'serverSide': true,
            'serverMethod': 'post',
            'ajax': {
                'url':'fetch.php',
            },
            'columns': [
                { data: 'id' },
                { data: 'name' },
                { data: 'address' },
                { data: 'gender' },
                { data: 'designation' },
                { data: 'age' },
            ]
        });
        $('select[name="multi_search_filter"]').on('change', function () {
            var multi_search_filter = $(this).val();
            if (multi_search_filter) {
                $.ajax({
                    type:'POST',
                    url:'fetch.php',
                    data:{multi_search_filter:multi_search_filter},
                    success: function (data) {
                        console.log(data);
                        $('#employee_data').html(data);
                    }
                });
            } else {
                return false;
            }
        });
    });
</script>

above code get data from fetch.php file and it will pass the data for  employee_data div


3.Create fetch.php file.
you can see below code as this way we got data from database and pass the data uisng jquery .
<?php
$con = mysqli_connect("localhost", "root", "", "datatablephp");

if(isset($_POST['draw'])){
    $draw = $_POST['draw'];
    $row = $_POST['start'];
    $rowperpage = $_POST['length']; // Rows display per page
    $columnIndex = $_POST['order'][0]['column']; // Column index
    $columnName = $_POST['columns'][$columnIndex]['data']; // Column name
    $columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
    $searchValue = $_POST['search']['value']; // Search value

// Search
    $searchQuery = " ";
    if($searchValue != ''){
        $searchQuery = " and (name like '%".$searchValue."%' or
        address like '%".$searchValue."%' or
        age like'%".$searchValue."%' ) ";
    }

// Total number of records without filtering

    $sel = mysqli_query($con,"select count(*) as allcount from tbl_employee");
    $records = mysqli_fetch_assoc($sel);
    $totalRecords = $records['allcount'];

// Total number of record with filtering

    $empQuery = "select * from tbl_employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
    $sel = mysqli_query($con,"select count(*) as allcount from tbl_employee WHERE 1 ".$searchQuery);
    $records = mysqli_fetch_assoc($sel);
    $totalRecordwithFilter = $records['allcount'];

// Fetch records
    $empQuery = "select * from tbl_employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
    $employeeRec = mysqli_query($con, $empQuery);
    $data = array();
    while ($row = mysqli_fetch_assoc($employeeRec)) {
        $data[] = array(
            "id"=>$row['id'],
            "name"=>$row['name'],
            "address"=>$row['address'],
            "gender"=>$row['gender'],
            "designation"=>$row['designation'],
            "age"=>$row['age']
        );
    }
    $output = array(
        "draw" => intval($draw),
        "iTotalRecords" => $totalRecords,
        "iTotalDisplayRecords" => $totalRecordwithFilter,
        "aaData" => $data
    );
    echo json_encode($output);
}

if(isset($_POST["multi_search_filter"])) {
    $multi_search_filter = $_POST['multi_search_filter'];
    $output = '';
    $query = "SELECT * FROM tbl_employee   WHERE  gender ='$multi_search_filter'";
    $result = mysqli_query($con, $query);
    $output .= '
           <div class="table-responsive">
        <table id="employee_data" class="table table-striped table-bordered">
               <tr >
               <td>Id</td>
                <td>Name</td>
                <td>Address</td>
                <td>Gender</td>
                <td>Designation</td>
                <td>Age</td>
            </tr>
      ';
    if(mysqli_num_rows($result) > 0)
    {
        while($row = mysqli_fetch_array($result))
        {$output .= '<tr style="background: aliceblue">
                          <td>'. $row['id'].'     </td>
                          <td>'. $row['name'].'     </td>
                          <td>'. $row['address'].'     </td>
                          <td>'. $row['gender'].'     </td>
                          <td>'. $row['designation'].'     </td>
                          <td>'. $row['age'].'     </td>
                     </tr>';}
    }
    else
    {$output .= '<tr><td colspan="10">Record Not Found</td></tr>';
    }
    $output .= '</div></table>';
    echo $output;
}
?>

4.Complete code for Displaying datatable in PHP
<?php
$con = mysqli_connect("localhost", "root", "", "datatablephp");?>

<!DOCTYPE html>
<html>
<head>
    <title>Datatables</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
<br /><br />

<div class="container">
    <h3 align="center">How to Create Datatable Using Ajax and PHP</h3>
    <br />
    <div class="col-md-6" style="display: flex;">
        <lable>Gender:</lable>
        <select class="form-control" name="multi_search_filter"  required>
            <option disabled selected value>Select an option</option>
            <?php
            $sql = 'SELECT DISTINCT gender FROM tbl_employee';
            $res = mysqli_query($con, $sql);
            if ($res):
                while ($row = mysqli_fetch_assoc($res)) {
                    echo '<option  value="'.$row['gender'].'">'.$row['gender'].'</option>';
                }
            endif; ?>
        </select>
    </div>
   <br />
   <br />
</div>
<div class="container">
    <div class="table-responsive">
        <table id="employee_data" class="table table-striped table-bordered">
            <thead>
            <tr>
                <td>Id</td>
                <td>Name</td>
                <td>Address</td>
                <td>Gender</td>
                <td>Designation</td>
                <td>Age</td>
            </tr>
            </thead>
        </table>
    </div>
</div>
</body>
</html>
<script>
    $(document).ready(function(){
        $('#employee_data').DataTable({
            'processing': true,
            'serverSide': true,
            'serverMethod': 'post',
            'ajax': {
                'url':'fetch.php',
            },
            'columns': [
                { data: 'id' },
                { data: 'name' },
                { data: 'address' },
                { data: 'gender' },
                { data: 'designation' },
                { data: 'age' },
            ]
        });
        $('select[name="multi_search_filter"]').on('change', function () {
            var multi_search_filter = $(this).val();
            if (multi_search_filter) {
                $.ajax({
                    type:'POST',
                    url:'fetch.php',
                    data:{multi_search_filter:multi_search_filter},
                    success: function (data) {
                        console.log(data);
                        $('#employee_data').html(data);
                    }
                });
            } else {
                return false;
            }
        });
    });
</script>

Related Post