How to Use DataTable in PHP

Do you want to integrate DataTable in your application? DataTable adds advanced interaction controls to your HTML tables. Plus it is open-source so everyone can use it for free. It is useful when your table has hundreds of entries. Using DataTable, you will get their built-in features like sorting, search, pagination, etc. In this article, I show you how to use DataTable in PHP.

DataTables is a popular choice for listing tabular records in the application. Due to its simplicity and easy installation developers preferred to use it.

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

DataTable

Getting Started

In order to get started, head over to your phpMyAdmin and create the table using the below query:

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The ‘users’ table has columns first_name, last_name, and age. Add dummy entries in this table. The user can use the Faker library to seed your table with dummy entries. 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 database connection.

config.php

<?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 DataTable in PHP

To integrate DataTable what I am going to do is fetch records from the database, loop through it, and display it in a table. And then apply DataTable to the HTML table.

Create a index.php file and add the code below in it.

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

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

In the above code, I fetched all rows from the ‘users’ table and assigned them to the PHP variable $arr_users. Now, I will loop through this variable and create the table row one by one.

<table id="userTable">
    <thead>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Age</th>
    </thead>
    <tbody>
        <?php if(!empty($arr_users)) { ?>
            <?php foreach($arr_users as $user) { ?>
                <tr>
                    <td><?php echo $user['first_name']; ?></td>
                    <td><?php echo $user['last_name']; ?></td>
                    <td><?php echo $user['age']; ?></td>
                </tr>
            <?php } ?>
        <?php } ?>
    </tbody>
</table>

Here, I gave an id ‘userTable’ to the table. The next thing I need to do is include the required files of DataTable in the HTML.

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

Finally, call the method DataTable() on the given table id.

<script>
$(document).ready(function() {
    $('#userTable').DataTable();
});
</script>

Our final code is as follows;

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

$sql = "SELECT id, first_name, last_name, age FROM users";
$result = $conn->query($sql);
$arr_users = [];
if ($result->num_rows > 0) {
    $arr_users = $result->fetch_all(MYSQLI_ASSOC);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Datatable</title>
    <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css"/>
</head>
<body>
    <table id="userTable">
        <thead>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
        </thead>
        <tbody>
            <?php if(!empty($arr_users)) { ?>
                <?php foreach($arr_users as $user) { ?>
                    <tr>
                        <td><?php echo $user['first_name']; ?></td>
                        <td><?php echo $user['last_name']; ?></td>
                        <td><?php echo $user['age']; ?></td>
                    </tr>
                <?php } ?>
            <?php } ?>
        </tbody>
    </table>
    <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script type="text/javascript" src="//cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
    <script>
    $(document).ready(function() {
        $('#userTable').DataTable();
    });
    </script>
</body>
</html>

I hope you may learn how to use DataTable in PHP. Please share your thoughts in the comment section below.

Related Articles

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

6 thoughts on “How to Use DataTable in PHP

Leave a Reply

Your email address will not be published. Required fields are marked *