-
Install DataTables: First, you need to download the DataTables library from their website and include it in your project.
-
Create a MySQL database: You'll need a MySQL database to store the data you want to display in the DataTable. Create a new database and a table to hold your data.
-
Create a PHP script to fetch data: You need to create a PHP script that will retrieve the data from the MySQL database and return it to DataTables. Here's an example:
Let's assume you have a MySQL database named example with a table named employees containing columns like id, name, position, and salary. We'll create a PHP script to handle server-side processing.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Server-side DataTables Example</title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
</head>
<body>
<table id="employeeTable" class="display" style="width:100%">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Position</th>
<th>Salary</th>
</tr>
</thead>
<tbody></tbody>
</table>
<script>
$(document).ready(function () {
$('#employeeTable').DataTable({
"processing": true,
"serverSide": true,
"ajax": "server.php", // Point to your server-side processing script
"columns": [
{"data": "id"},
{"data": "name"},
{"data": "position"},
{"data": "salary"}
]
});
});
</script>
</body>
</html>
Create a php file and name it server.php
server.php (Server-side script to handle DataTables server-side processing):
<?php
$host = "localhost";
$dbname = "example";
$username = "your_username";
$password = "your_password";
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$draw = $_POST['draw'];
$start = $_POST['start'];
$length = $_POST['length'];
$columns = $_POST['columns'];
$order = $_POST['order'][0];
$columnIndex = $order['column'];
$columnName = $columns[$columnIndex]['data'];
$columnSortOrder = $order['dir'];
$stmt = $conn->prepare("SELECT * FROM employees ORDER BY $columnName $columnSortOrder LIMIT $start, $length");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$totalRecords = $conn->query("SELECT COUNT(*) FROM employees")->fetchColumn();
$totalFiltered = $totalRecords;
$response = array(
"draw" => intval($draw),
"recordsTotal" => intval($totalRecords),
"recordsFiltered" => intval($totalFiltered),
"data" => $result
);
echo json_encode($response);
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
This example demonstrates a basic server-side processing implementation for DataTables using PHP and MySQL. It retrieves the data from the MySQL database, processes it according to DataTables requirements, and sends the JSON response back to the client for rendering. Make sure to replace placeholders like your_username and your_password with your actual MySQL credentials.
