How to Use DataTables in PHP

Do you want to integrate DataTables in your application? DataTables adds advanced interaction controls to your HTML tables. DataTables is a popular choice 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, etc.

The DataTables supports 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 processing is done in the browser.
  • Server-side processing: The Ajax request is made for every table redraw, with only the data required for each display returned. Go for this solution if you have a larger data to display 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 in the DataTables. The final output will look like as below screenshot:

datatable-records

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() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The users table has columns fullname, and email which I’ll display in the HTML. In order to display records, add dummy entries in this table. The user can use the Faker library to seed database tables with fake records. Using the faker library one can add thousands of fake entries in the database within a few moments.

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

<?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.

How to Use DataTables in PHP

As I said before, we are going to see both processing modes – client-side and server-side. 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/v/dt/dt-1.11.5/datatables.min.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.11.5/datatables.min.js"></script>

We will give an id to the HTML table and call DataTable() on this id attribute. Create the index.php file and add the code below to it.

<?php
require_once('config.php');
 
$sql = "SELECT fullname, email FROM users";
$result = $conn->query($sql);
$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/v/dt/dt-1.11.5/datatables.min.css" />
<table id="tblUser">
    <thead>
        <th>Fullname</th>
        <th>Email</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>
                </tr>
            <?php } ?>
        <?php } ?>
    </tbody>
</table>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.11.5/datatables.min.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 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 this mode, we didn’t fetch records upfront. Instead, we get the records in chunks from the server-side. It reduces the load on the browser for large datasets.

Let’s create a demo.php file and add the below code to it.

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.11.5/datatables.min.css" />
<table id="tblUser">
    <thead>
        <tr>
            <th>Fullname</th>
            <th>Email</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>Fullname</th>
            <th>Email</th>
        </tr>
    </tfoot>
</table>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.11.5/datatables.min.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 key. 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: It will be used to 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 entered value in 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 fullname, email FROM users";

if (isset($_GET['search']) && !empty($_GET['search']['value'])) {
    $search = $_GET['search']['value'];
    $sql .= " WHERE fullname like '%$search%' OR email like '%$search%'";
}

$sql .= " LIMIT $start, $length";

$query = $conn->query($sql);
$result = [];
while ($row = $query->fetch_assoc()) {
    $result[] = [
        $row['fullname'],
        $row['email'],
    ];
}

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

Now give it a try. You will see records are not coming in bulk but in chunks. It will help to improve performance and reduce the load on the browser.

I hope you may learn 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

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

9 thoughts on “How to Use DataTables in PHP

Leave a Reply

Your email address will not be published.