Hi Dev,

Today, i am going to share with you how to utilize jQuery datatable plugin in php mysql project. i will additionally describe small example of data table with server side scripting, here i will utilize PHP and MySQL to get data from server side. So you have to simple follow few step and will get small expeditious example.

DataTables is a jquery library that provide pagination, instant search and multi-column ordering to HTML table. DataTables easily use with normal jquery ui project, bootstrap. We can simply get records from server side using ajax.

In this example, i created "users" table with id, name, email field in mysql database. i will create index.html page run ajax request to server and server side fetch that data and display on that page using datatables. It is very simple example, using this you can make more complex too.

So after run example successfully, you will get layout like as bellow attach screen shot. Just follow few step and get layout.

In first step we will create new new table "users" in database. You can use following SQL Query for create "users" table, after create "users" table make sure you have to add some dummy records. So let's create using bellow sql query:

users table:

CREATE TABLE `product` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_price` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_category` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_details` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_stock` int(11) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

In this step, we require to create database configuration file, here we will set database name, username and password. So let's create "config.php" file on your root directory and put bellow code:

config.php

<?php

    define (DB_USER, "root");
    define (DB_PASSWORD, "root");
    define (DB_DATABASE, "php_crud");
    define (DB_HOST, "localhost");


    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);


?>

In this step, we will create index.html file, in this file we will write code for display data using datatables library. So let's create index.html file on your root directory and put bellow code.

    



   
   
   
   
   




  
PHP - Jquery Datatables Example - mywebtuts.com
  

    

      

          
Id
          
product_name
          
product_price
          
product_category
          
product_details
          
product_stock
      
    
  

In last step, we need to create pro.php file, in this file we will write sql query and fetch records from mysql database.

pro.php

<?php

    require('config.php');


    $sql = "SELECT * FROM product";
    $result = $mysqli->query($sql);


    while($row = $result->fetch_array(MYSQLI_ASSOC)){
      $data[] = $row;
    }


    $results = ["sEcho" => 1,
                "iTotalRecords" => count($data),
                "iTotalDisplayRecords" => count($data),
                "aaData" => $data ];


    echo json_encode($results);

?>

Now we are ready to run our example so run bellow command for quick run:

php -S localhost:8000

Now you can open bellow URL on your browser:

http://localhost:8000/index.html

I Hope It will help you..