Code And Deploy
Posted on April 20, 2022
Originally posted @ https://codeanddeploy.com visit and download the sample code: https://codeanddeploy.com/blog/php/jquery-datatables-ajax-php-and-mysql-using-pdo-example
In this post, I will share an example of how to implement jquery Datatables ajax in PHP and MySQL using PDO. If you have hundreds of thousands of records or even millions of records you don't want to load it at once to your HTML as we do in our previous example because it could slow your server performance. But using ajax you don't need to load all the records to your Datatable because it is paginated and you only show what we need.
Step 1: Create Table with MySQL
We will use the employee's table as an example with basic fields. See below table structure:
CREATE TABLE `employees` (
`id` int(10) NOT NULL,
`email` varchar(100) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`address` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `employees`
ADD PRIMARY KEY (`id`);
ALTER TABLE `employees`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;
Step 2: PHP & MySQL Database Connection
Once your database and table are configured let's create a connection. See below:
<?php
$host = 'localhost';
$db = 'demos';
$user = 'root';
$password = '';
$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";
try {
$conn = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
} catch (PDOException $e) {
echo $e->getMessage();
}
As you can see our connection is coded with PDO.
Step 3: Index with Datatables
Now let's configure our HTML with Datatable and Ajax script. See below code:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>JQuery Datatable Example</title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#jquery-datatable-ajax-php').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'datatable.php'
},
'columns': [
{ data: 'email' },
{ data: 'first_name' },
{ data: 'last_name' },
{ data: 'address' }
]
});
} );
</script>
</head>
<body>
<div class="container mt-5">
<h2 style="margin-bottom: 30px;">jQuery Datatable Ajax PHP Example</h2>
<table id="jquery-datatable-ajax-php" class="display" style="width:100%">
<thead>
<tr>
<th>Email</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Address</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
Step 4: Our Datatables PHP & MySQL Code with PDO
Next, our code for our Datatables Ajax PHP and MySQL code using PDO.
<?php
// Database Connection
include 'connection.php';
// Reading value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value
$searchArray = array();
// Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery = " AND (email LIKE :email OR
first_name LIKE :first_name OR
last_name LIKE :last_name OR
address LIKE :address ) ";
$searchArray = array(
'email'=>"%$searchValue%",
'first_name'=>"%$searchValue%",
'last_name'=>"%$searchValue%",
'address'=>"%$searchValue%"
);
}
// Total number of records without filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM employees ");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];
// Total number of records with filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM employees WHERE 1 ".$searchQuery);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];
// Fetch records
$stmt = $conn->prepare("SELECT * FROM employees WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");
// Bind values
foreach ($searchArray as $key=>$search) {
$stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}
$stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$empRecords = $stmt->fetchAll();
$data = array();
foreach ($empRecords as $row) {
$data[] = array(
"email"=>$row['email'],
"first_name"=>$row['first_name'],
"last_name"=>$row['last_name'],
"address"=>$row['address']
);
}
// Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
Result:
I hope this tutorial can help you. Kindly visit here https://codeanddeploy.com/blog/php/jquery-datatables-ajax-php-and-mysql-using-pdo-example if you want to download this code.
Happy coding :)
Posted on April 20, 2022
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related