PHP – jquery datatables with mysql database example from scratch
In this post we will give you information about PHP – jquery datatables with mysql database example from scratch. Hear we will give you detail about PHP – jquery datatables with mysql database example from scratchAnd how to use it also give you demo for it if it is necessary.
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);
?>
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 >
<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:
http://localhost:8000/index.html
I hope it can help you….
Hope this code and post will helped you for implement PHP – jquery datatables with mysql database example from scratch. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs