DataTables example with PHP and PDO MySQL

DataTables is an advanced table plugin for jQuery which easily brings features like pagination, searching and ordering for many data sources.

This post is showing how to use DataTables with PHP and MySQL.

For DataTables with Ajax.

Keep in mind that naturally and especially for larger databases doing a direct PHP to MySQL call would be slower than reading from a cached JSON file…

To use DataTables you will need jQuery and optionally some styling scripts and CSS:

<head>
    <title>datatables example</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.3/css/bootstrap.min.css"/>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/js/jquery.dataTables.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/js/dataTables.bootstrap4.min.js"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/css/dataTables.bootstrap4.min.css"/>
</head>

Here I have included Bootstrap.

The base HTML with the table thead and the PHP PDO MySQL query. The query used will fetch all the ‘animals’ from my mock table and order by their id.

<div class="container">
    <div class="row">
        <div class="col-12">
            <div class="card p-2">
                <h3 align="center">Animals</h3>
                <div class="table-responsive">
                    <table id="all_animals" class="table table-striped table-bordered">
                        <thead>
                        <tr>
                            <td>Id</td>
                            <td>GUID</td>
                            <td>Name</td>
                            <td>Status</td>
                        </tr>
                        </thead>
                        <?php
                        $select = $db->prepare("SELECT * FROM `MOCK_DATA` ORDER BY `id`;");//Query
                        $select->execute();
                        while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
                            echo '<tr>';
                            echo '<td>' . $row["id"] . '</td>';
                            echo '<td>' . $row["guid"] . '</td> ';
                            echo '<td>' . $row["name"] . '</td>';
                            echo '<td>' . $row["status"] . '</td>';
                            echo '</tr>';
                        }
                        ?>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

Finally, put the DataTables initiator at the bottom of the page:

<script type="application/javascript">
    $(document).ready(function () {
        $('#all_animals').DataTable({"pageLength": 10, "order": []});
    });
</script>

Included are the options for 10 items to be shown each page and no ordering because I used the MySQL query to do the ordering.

If not using the database to order I would have used:

<script type="application/javascript">
    $(document).ready(function () {
        $('#all_animals').DataTable({"pageLength": 10, "order": [[0, 'asc']]});
    });
</script>

As 0 is the index for ‘id’ and asc for ascending.