Monday, 27 June 2016

Server Side Datatable handling using PHP and MySql

Server Side Datatable handling using PHP and MySql

jQuery datatable server side processing. Using jQuery Ajax and MySql.


Step 1: View Layout for display Student Record

index.html 

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>Server Side Datatable handling</title>
        <link rel="stylesheet" id="font-awesome-style-css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" type="text/css" media="all">
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css"/>        
        <script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.11.1.min.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
    </head>
    <body>
        <div class="container">
            <h1>Student Record</h1>
            <table id="student_grid" class="display" width="100%" cellspacing="0">
                <thead>
                    <tr>
                        <th>Student_id</th>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Age</th>
                    </tr>
                </thead>
         
                <tfoot>
                    <tr>
                       <th>Student_id</th>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Age</th>
                    </tr>
                </tfoot>
            </table>
        </div>
        <script type="text/javascript">
        $( document ).ready(function() {
            $('#student_grid').DataTable({
                "pageLength": 20, // Now of rows on Page
                "bProcessing": true,
                "serverSide": true,
                "ajax":{
                    url :"user_response.php", // json datasource
                    type: "post",  // type of method  ,GET/POST/DELETE
                    error: function(){
                        $("#student_grid_processing").css("display","none");
                    }
                }
            });   
        });
        </script>
    </body>
</html>

Step 2: Handing Server Side Student record processing.

user_response.php

<?php
//include database connection file 
    include_once("db.php");
    // initilize all variable
    $params = $columns = $totalRecords = $data = array();
    $params = $_REQUEST;
    //define index of column name
    $columns = array( 
        0 =>'id',
        1 =>'student_name', 
        2 => 'student_email',
        3 => 'student_age'
    );

    $where = $sqlTot = $sqlRec = "";

    // check search value exist
    if( !empty($params['search']['value']) ) {   
        $where .=" WHERE ";
        $where .=" ( student_name LIKE '".$params['search']['value']."%' ";    
        $where .=" OR student_email LIKE '".$params['search']['value']."%' ";

        $where .=" OR student_age LIKE '".$params['search']['value']."%' )";
    }

    // getting total number records without any search
    $sql = "SELECT * FROM `students` ";
    $sqlTot .= $sql;
    $sqlRec .= $sql;
    
    //concatenate search sql if value exist
    if(isset($where) && $where != '') {

        $sqlTot .= $where;
        $sqlRec .= $where;
    }


     $sqlRec .=  " ORDER BY ". $columns[$params['order'][0]['column']]."   ".$params['order'][0]['dir']."  LIMIT ".$params['start']." ,".$params['length']." ";

    $queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));


    $totalRecords = mysqli_num_rows($queryTot);

    $queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");

    while( $row = mysqli_fetch_row($queryRecords) ) { 
        $data[] = $row;
    }    

    $json_data = array(
            "draw"            => intval( $params['draw'] ),   
            "recordsTotal"    => intval( $totalRecords ),  
            "recordsFiltered" => intval($totalRecords),
            "data"            => $data   // total data array
            );

    echo json_encode($json_data);  // send data as json format

Step 3: MySqli Database connectivity with PHP

db.php

/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

0 comments:

Post a Comment