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

14
02Datatable - 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

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 Thirumani Raj posted on - 14th Feb 2016