May 15, 2024 | PHP jQuery Ajax
Hello Dev,
"Today, I'm excited to demonstrate how to integrate the jQuery DataTables plugin into a PHP MySQL project. I'll provide a concise example of implementing a data table with server-side scripting, leveraging PHP and MySQL to fetch data.
DataTables is a versatile jQuery library that offers features like pagination, instant search, and multi-column ordering for HTML tables. It seamlessly integrates with various frameworks such as jQuery UI and Bootstrap. With DataTables, fetching records from the server side via AJAX is straightforward.
In this demonstration, I've set up a 'users' table in MySQL, containing fields for ID, name, and email. The process involves creating an 'index.html' page that sends an AJAX request to the server. On the server side, we fetch the data and display it on the page using DataTables. While this example is simple, it serves as a foundation for more complex implementations.
Upon successfully implementing this example, you'll achieve a layout similar to the attached screenshot. Follow a few simple steps, and you'll be up and running in no time!"
To integrate jQuery DataTables with a PHP MySQL project for server-side processing, follow these steps. This example assumes you have a "users" table with columns id, name, and email.
Read Also: Laravel 10 - Sort By Column with pagination example Step 1: Create index.php index.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>DataTables Example</title>
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<!-- jQuery -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<!-- DataTables CSS -->
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.3/css/dataTables.bootstrap4.min.css">
</head>
<body>
<div class="container">
<h1>Users Data</h1>
<table id="example" class="table table-striped table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<!-- DataTables JS -->
<script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.11.3/js/dataTables.bootstrap4.min.js"></script>
<script>
$(document).ready(function() {
$('#example').DataTable({
processing: true,
serverSide: true,
ajax: 'fetch_data.php',
columns: [
{ data: 'id', name: 'id' },
{ data: 'name', name: 'name' },
{ data: 'email', name: 'email' }
]
});
});
</script>
</body>
</html>
Read Also: How to Create Jquery Dialog Box Popup Model Example
Step 2: Create the Server-Side Script (fetch_data.php)
Create a PHP file named fetch_data.php in the root of your project. This script will connect to your MySQL database, retrieve the data, and send it back to DataTables:
<?php
header("Content-Type: application/json");
$host = "localhost";
$db = "your_database_name";
$user = "root"; // or your username
$pass = ""; // or your password
$charset = "utf8mb4";
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);
$sql = "SELECT * FROM users";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
echo json_encode($result);
?>
Make sure to replace "your_database_name", "root", and "" with your actual database name, username, and password.
Now, open your web browser and enter the provided URL to view the output of the application.
View Your Applicationphp -S localhost:8000
http://localhost:8000/index.htmlConclusion
After completing these steps, you should have a working example of jQuery DataTables with server-side processing using PHP and MySQL. When you load the page, DataTables will automatically fetch data from the fetch_data.php script and populate the table accordingly. This setup provides a robust foundation for displaying dynamic data in web applications.
Thank you for your encouragement! If you have any questions or need further assistance, feel free to ask. I'm here to help!
"Hey there! I'm a full-stack developer and proud owner of ItErrorSolution.com, based right here in India. I love nothing more than sharing handy tips and tricks with my fellow developers through easy-to-follow tutorials. When it comes to coding, I'm all about PHP, Laravel, Angular, Vue, Node, JavaScript, jQuery, CodeIgniter, and Bootstrap – been hooked on them forever! I'm all about putting in the work and staying committed. Ready to join me on this journey to coding?"