Do you want to integrate DataTables into your application? DataTables adds advanced interaction controls to your HTML tables. This service is popular for listing tabular records in the application. Due to its simplicity and easy installation, developers preferred to use it. It is open-source so everyone can use it for free. Even if you have an immense number of records the DataTables would handle it smoothly. Using DataTables, you will get their built-in features like sorting, search, pagination, and much more for the HTML tables.

The DataTables support two modes of processing data.

• Client-side processing: The full data set is loaded up-front. It’s ideal for a small number of records as data is processed in the browser.
• Server-side processing: The Ajax request is made for every table redraw, with only the data required for each display returned. Use this solution if you have larger data because the data processing is performed on the server.

In this article, I will show you how to use DataTables in PHP. We’ll discuss both processing modes practically.

For our tutorial, I will create a table in the database and will display its records using the DataTables. The final output will look like as below screenshot.

Getting Started

In order to get started, head over to your database and create the users table using the below SQL.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The users table has columns fullname, and email which I’ll display in the HTML table. I’ll also add action links(edit and delete) which can be useful to process table rows independently.

Assuming you don’t have records to display, let’s add dummy entries in the users table. I’ll use the Faker library to seed a database table with fake records. Using the faker library one can add thousands of fake entries to the database within a few moments. Install this library using the command below.

composer require fakerphp/faker

Next, create a config.php file and write a code for the database connection.

<?php
require_once "vendor/autoload.php";

$conn = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
 
if ($conn->connect_errno) {
    echo "Error: " . $conn->connect_error;
}

Make sure to replace the placeholders with the actual values. After this, write the code below into a PHP file say faker.php to seed your database table.

<?php
require_once "config.php";

$faker = Faker\Factory::create();
  
for ($i=0; $i < 200; $i++) {
    $sql = sprintf("INSERT INTO users(fullname, email) VALUES('%s', '%s')", $conn->real_escape_string($faker->name()), $conn->real_escape_string($faker->email()));
    $conn->query($sql);
}

echo "Fake entries inserted successfully.";

Here, I am inserting 200 rows into the users table. You can adjust this value. Run this file and you’ll have 200 dummy records in the database table.

How to Use DataTables in PHP

As we are going to see both client-side and server-side processing modes, let’s start with the client-side processing.

Client-Side Processing with DataTables

In this mode, I’ll fetch records from the database, loop through it, and display them in the HTML table. When we call the DataTable() function on this HTML table, it will get automatically converted and has all the benefits of DataTables.

To call DataTable(), it requires the following scripts and style.

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.js"></script>

I’ll give an id to the HTML table and call the method DataTable() on this id attribute.

<?php
require_once('config.php');

$result = $conn->query("SELECT id, fullname, email FROM users");
$arr_users = [];
if ($result->num_rows > 0) {
    $arr_users = $result->fetch_all(MYSQLI_ASSOC);
}
?>

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css" />
<table id="tblUser">
    <thead>
        <th>Fullname</th>
        <th>Email</th>
        <th>Action</th>
    </thead>
    <tbody>
        <?php if(!empty($arr_users)) { ?>
            <?php foreach($arr_users as $user) { ?>
                <tr>
                    <td><?php echo $user['fullname']; ?></td>
                    <td><?php echo $user['email']; ?></td>
                    <td><a href="edit.php?id=<?php echo $user['id']; ?>">Edit</a> | <a href="delete.php?id=<?php echo $user['id']; ?>">Delete</a></td>
                </tr>
            <?php } ?>
        <?php } ?>
    </tbody>
</table>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.js"></script>
<script>
jQuery(document).ready(function($) {
    $('#tblUser').DataTable();
} );
</script>

In the above code, I fetched all rows from the users table and assigned them to the PHP variable $arr_users. I loop through this variable and fill up the rows of the HTML table. I added an id tblUser to the table. On this id attribute, I called the method DataTable() which converts the HTML table into the DataTables.

Server-Side Processing with DataTables

In the case of server-side mode, we don’t fetch records upfront. Instead, we get the records in chunks from the server. It reduces the load on the browser for large datasets.

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css" />
<table id="tblUser">
    <thead>
        <tr>
            <th>Fullname</th>
            <th>Email</th>
            <th>Action</th>
        </tr>
    </thead>
</table>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.js"></script>
<script>
jQuery(document).ready(function($) {
    $('#tblUser').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "server_processing.php"
    } );
} );
</script>

Here, inside the DataTable() method I passed the server_processing.php to the ajax parameter. That means in this server_processing.php file we have to write a code to fetch records in chunks.

At this moment, if you go to the Network tab of the Browser console, you will notice a few parameters are sent to the server-side script. Some of them are:

• length: Determine how many records to return.
• start: Starting point to fetch records from.
• search: If you want to filter records, this key will have the value from the search field.

Write the code below to the server_processing.php file.

<?php
require_once('config.php');
 
$query = $conn->query("SELECT count(id) FROM users");
$totalRecords = $query->fetch_row()[0];
 
$length = $_GET['length'];
$start = $_GET['start'];
 
$sql = "SELECT id, fullname, email FROM users";
 
if (isset($_GET['search']) && !empty($_GET['search']['value'])) {
    $search = $_GET['search']['value'];
    $sql .= sprintf(" WHERE fullname like '%s' OR email like '%s'", '%'.$conn->real_escape_string($search).'%', '%'.$conn->real_escape_string($search).'%');
}

$sql .= " LIMIT $start, $length";
$query = $conn->query($sql);
$result = [];
while ($row = $query->fetch_assoc()) {
    $result[] = [
        $row['fullname'],
        $row['email'],
        "<a href='edit.php?id=".$row['id']."'>Edit</a> | <a href='delete.php?id=".$row['id']."''>Delete</a>"
    ];
}

echo json_encode([
    'draw' => $_GET['draw'],
    'recordsTotal' => $totalRecords,
    'recordsFiltered' => $totalRecords,
    'data' => $result,
]);

Now you can test the flow. You will see records are not coming in bulk but in chunks. This approach will help to improve performance and reduce the load on the browser. All operations can be seen in the Network tab of the browser.

I hope you learned how to use DataTables in PHP. This tutorial should help you to integrate DataTables into your website. Please share your thoughts and suggestions in the comment section below.

Related Articles

• How to Convert HTML to PDF in PHP
• How to Export MySQL Database Data to Excel Using PHP
• How to Read CSV and Excel File in PHP Using PhpSpreadsheet

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.