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();
}

Author Description

Coding Cheatsheets - Learn to Code