14
02
Datatable - Searching, Sorting and Pagination on server side using Php , Mysql, Ajax, Json

Hi guys this tutorial will explain about datatable searching sorting and pagination on server side using php, ajax, mysql,  json. In web development, developers are required to populate the data in the structured manner which can be easily search, sort and paginate. Jquery datatable can be easily integrate with bootstrap also. The data are represents in the table format. In this we used php as the server side scripting to get the data from the mysql and the response data can be populated by json in table.Datatable-php-jquery-ajax-json-mysql

Some of the features are:

  • Pagination - next, previous, showing total number of count.
  • Search - searching by all column
  • Sort - sort data by any column wise
  • HTML Code:

    
    
    
        
        
    
    
    
    ID NAME PHONE Email Country ZIP Code
    ID NAME PHONE Email Country ZIP Code

    JQuery

    $(document).ready(function () {
            $('#users').DataTable({
                "columns": [
                    {"data": "id"},
                    {"data": "name"},
                    {"data": "phone"},
                    {"data": "email"},
                    {"data": "country"},
                    {"data": "zip"}
                ],
                "processing": true,
                "serverSide": true,
                "ajax": {
                    url: 'users.php',
                    type: 'POST'
                }
            });
        });
    

    PHP code:

    * Database Configuration and $con using mysqli
         */
    
        define("HOST", "localhost");
        define("USER", "root");
        define("PASSWORD", "");
        define("DB", "datatables");
        define("MyTable", "users");
    
        $con = mysqli_connect(HOST, USER, PASSWORD, DB) OR DIE("Error in  connecting DB : " . mysqli_connect_error());
    
        /* END DB Config and $con */
    function getData($sql){
            global $con ;//Use $con that already opened
            $query = mysqli_query($con, $sql) OR DIE ("Sorry! can't get data. Check your Query" );
            $data = array();
            foreach ($query as $row ) {
               	$data[] = array_merge($row, array($row['id']));
    			
            }
    		
            return $data;
        }
    

    Download and look on it our full code :)

    By posted on - 14th Feb 2016