Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

Generate XML file in Cakephp

Steps to Generate XML file using CakePHP: Step-1 Enable to parse xml extension in config route.php file.     Router::parseExtensions('xml'); Step-2 Add Request Handler Component to the Controller    var $components = array(‘RequestHandler’); Step-3 Add controller Action For XML Generation in Post Controller     function generateXMLFile()     {         if ($this->RequestHandler->isXml()) { // check request type             $this->layout = 'empty'; // create an empty layout in app/views/layouts/empty.ctp              }        }  Add header code in empty layout <?php header('Content-type: text/xml');?> <?php echo $this->Xml->header(); ?> <?php echo $content_for_layout; ?> Step-4 Set up View To generate XML Create xml folder inside Posts vi...
53 comments
Read more

How To Create Shortcodes In WordPress

We can create own shortcode by using its predified hooks add_shortcode( 'hello-world', 'techsudhir_hello_world_shortcode' ); 1. Write the Shortcode Function Write a function with a unique name, which will execute the code you’d like the shortcode to trigger: function techsudhir_hello_world_shortcode() {    return 'Hello world!'; } Example: [hello-world] If we were to use this function normally, it would return Hello world! as a string 2. Shortcode function with parameters function techsudhir_hello_world_shortcode( $atts ) {    $a = shortcode_atts( array(       'name' => 'world'    ), $atts );    return 'Hello ' . $a['name'] . !'; } Example: [hello-world name="Sudhir"] You can also call shortcode function in PHP using do_shortcode function Example: do_shortcode('[hello-world]');
Post a Comment
Read more

How to replace plain URLs with links

Here we will explain how to replace Urls with links from string Using PHP $string ='Rajiv Uttamchandani is an astrophysicist, human rights activist, and entrepreneur. Academy, a nonprofit organization dedicated to providing a robust technology-centered education program for refugee and displaced youth around the world.  CNN Interview - https://www.youtube.com/watch?v=EtTwGke6Jtg   CNN Interview - https://www.youtube.com/watch?v=g7pRTAppsCc&feature=youtu.be'; $string = preg_replace('@(https?://([-\w\.]+)+(:\d+)?(/([\w/_\.%-=#]*(\?\S+)?)?)?)@', '<a href="$1">$1</a>', $string); Using Javascript <script> function linkify(inputText) {     var replacedText, replacePattern1, replacePattern2, replacePattern3;     //URLs starting with http://, https://, or ftp://     replacePattern1 = /(\b(https?|ftp):\/\/[-A-Z0-9+&@#\/%?=~_|!:,.;]*[-A-Z0-9+&@#\/%=~_|])/gim;     replacedText = inputT...
1 comment
Read more