PHP - Jquery Datatables With Mysql Database Example Tutorial

Apr 27, 2021 . Admin

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.

Step 1: Create users table

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;
Step 2: Create config.php File

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:



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

Step 3: Create index.html File

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 -

PHP - Jquery Datatables Example -

Id product_name product_price product_category product_details product_stock
Step 4: Create pro.php File

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




    $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:


I Hope It will help you..