PHP MySQL DataTables Server-side Processing Example

By Hardik Savani May 14, 2024 Category : PHP jQuery MySql Ajax

Today, i am going to share with you how to use jQuery datatable plugin in php mysql project. i will also describe small example of data table with server side scripting, here i will use PHP and MySQL to get data from server side. So you have to simple follow few step and will get small quick 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.

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 `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) 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:

config.php

<?php

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

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

?>

Read Also: PHP AngularJS CRUD with Search and Pagination Tutorial

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.

index.html

<!DOCTYPE html>
<html>
<head>
   <title>PHP - Jquery Datatables Example</title>
   <script type="text/javascript" src="https://code.jquery.com/jquery-1.9.1.min.js"></script>
   <link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
   <script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
</head>
<body>

<div class="container">
  <h2>PHP - Jquery Datatables Example</h2>
  <table id="my-example">
    <thead>
      <tr>
          <th>Id</th>
          <th>Name</th>
          <th>Email</th>
      </tr>
    </thead>
  </table>
</div>

</body>

<script type="text/javascript">
  $(document).ready(function() {
      $('#my-example').dataTable({
        "bProcessing": true,
        "sAjaxSource": "pro.php",
        "aoColumns": [
              { mData: 'id' } ,
              { mData: 'name' },
              { mData: 'email' }
            ]
      });  
  });
</script>
</html>

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.

pro.php

&tl;?php

require('config.php');

$sql = "SELECT * FROM users";
$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);

 
?>

Let's run bellow command on your root directory for quick run:

php -S localhost:8000

Now you can open bellow URL on your browser:

Read Also: How to Remove Multiple Keys from PHP Array?
http://localhost:8000/index.html

I hope it can help you....

Tags :
Previous

Laravel CURL Request Example using Ixudra/curl
Next

Laravel Bootstrap Tag System Example Tutorial

We are Recommending you

Popular Posts

Categories

Latest Posts

Shares