Datatable PHP MySQL Example

Datatable PHP MySQL Example is a simple tutorial on how to use datatable and jquery plugin with PHP & MySQL to display data in a HTML table.

Datatable is a jquery plugin which is very flexible for displaying data in HTML table. Some of the benefits of using datatable includes:

1. Exclusion of writing pagination code while displaying data in a table format because datatable does that for you.
2. Exclusion of writing your own search code while displaying data in a table format. Datatable does that for you.
3. Datatable takes care of code for column ordering and sorting.

Reference The Plugin

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.15/css/dataTables.jqueryui.min.css"/>

<script type="text/javascript" src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>

<script type="text/javascript" src="https://cdn.datatables.net/1.10.15/js/dataTables.jqueryui.min.js"></script>

The JQuery Script

<script>
    $(document).ready(function(){
    $('#Your_Table_ID_Here').DataTable({
    pageLength : 5,
          lengthMenu: [[5, 10, 20, - 1], [5, 10, 20, 'Todos']],
          "order": [[ 0, "desc" ]]
    });
</script>

By default, jquery datatable displays 10 records. I have used this line of code

pageLength : 5,

to change it to 5 records (change it to whatever you prefer in your code).

Datatable allows you choose the numbers of records you would prefer to display to users at once. By default, datatables allows users choose to display either : 10,25,50 or 100 records at once. To change the options and make it display either 5 or 10 or 20 or all the records at once, use the line of code,

lengthMenu: [[5, 10, 20, - 1], [5, 10, 20, 'Todos']],

and to order by the first column in a descender order, we use

"order": [[ 0, "desc" ]]

Play around with the numbers in LengthMenu to have datatable display whatever options you may wish.

HTML And PHP Code

<div class="box-body table-responsive no-padding">
  <table id="Your_Table_ID_Here" class="table table-striped table-bordered table-hover table-sm w-auto">
     <thead>
        <tr>
           <th scope="col">S.No</th>
           <th scope="col">Username</th>
           <th scope="col">Affiliate ID</th>
           <th scope="col">Referrals Option</th>
        </tr>                                                  
     </thead>
     <tbody>
        <?php
          foreach ($allRefs as $key => $inst) {
            $sN = $key + 1;
        ?>
            <tr>
              <td scope="row"><?= $sN ?></td>
              <td><?= htmlentities($username, ENT_QUOTES, 'UTF-8') ?></td>
              <td><?= (INT) $Affiliate_ID ?></td>
              <td><?= htmlentities($referrals_option, ENT_QUOTES, 'UTF-8') ?></td>
            </tr>
        <?php
          }
        ?>
     </tbody>
   </table>
</div>

The table id must be set, it is what the jquery code above will use for binding. The table consist of the Table Header <thead> where the Column names are defined and the table body <tbody> where the actual data is displayed.

So here we have 4 columns defined: S.No, Username, Affiliate ID and Referrals Option as seen in this block of code extracted from the code above

<thead>
        <tr>
           <th scope="col">S.No</th>
           <th scope="col">Username</th>
           <th scope="col">Affiliate ID</th>
           <th scope="col">Referrals Option</th>
        </tr>                                                  
</thead>

And then in the below block of code (also extracted from the code above),

<tr>
              <td scope="row"><?= $sN ?></td>
              <td><?= htmlentities($username, ENT_QUOTES, 'UTF-8') ?></td>
              <td><?= (INT) $Affiliate_ID ?></td>
              <td><?= htmlentities($referrals_option, ENT_QUOTES, 'UTF-8') ?></td>
</tr>

we put real data that are fetch from MySQL table in-between the <td></td> tags which are under the <tr> tag inside the <tbody>

I don’t know what method of sql you prefer to use to retrieve data from mysql table but i prefer PDO and as such i show an example PDO below which can be used to retrieved the values to be displayed from the database. The values retrieved and binded to the php variables e $username, $Affiliate_ID and $referrals_option.

try{
            $query = "SELECT username, affiliate_id, referrals_options FROM soso_table";
            $q = $yourConnectionString->prepare($query);
            $q->execute();//execute query
            $q->bindColumn(1, $username);
            $q->bindColumn(2, $Affiliate_ID);
            $q->bindColumn(3, $referrals_option);
            $q->fetch();//fetch result
}catch(PDOexception $e){
            //deal with any errors here
}

This variables can then be supplied in-between the <td></td> tag as needed.

Conclusion

This is the image of what a datatable populated with data from the database looks like. Yours may differ depending on the no. of columns you choose to display.

Click on Show entries field under Users in the image above to see what options you have – what i set it to in the code above was

lengthMenu: [[5, 10, 20, - 1], [5, 10, 20, 'Todos']],

• 5 – displays 5 records at once
• 10 – displays 10 records at once
• 20 – displays 20 records at once
• Todos – displays all the record at once

Note that my image display only 4 records; that is because i set my pageLenght to 4 so that i could capture the image in one piece and let you see the pagination below.

As you can see from the image, the pagination is already set for you; the search code is already set for you and you can order by any column you want and sort either by Asc (ascending order) or Desc (descending order).

You can search using combined keywords from several columns to get specific resultsets. For instance i can combine this values in a single search to retrieve Gospelcity record:

Gospelcity 0703102

More resources are available here https://datatables.net/ for you to play around with and get more and more acquainted with datatables.