Easy mysql results pagination

Today, I’m going to introduce to you a jquery plugin called DataTables. Here’s a description of DataTables from datatables.net:

DataTables is a plug-in for the jQuery Javascript library.
It is a highly flexible tool, based upon the foundations of progressive enhancement,
which will add advanced interaction controls to any HTML table.

Okay, so you didn’t see any php in there and only html. But if you know php, then you already know that php generates html. So we can actually use this plugin together with php. And in this tutorial, were going to use it to paginate, sort, and limit the number of records shown in an html page. Pretty awesome right? And this all happens in the client-side.

Looking back

If you still remember. Last time I showed you how to paginate mysql results in php:

Paginate mysql results in php

Well, that get’s the job done in the server-side. This time, I’m going to show you how to get it done on the client-side with lots of features added by using DataTables.

Assumptions

I assume that you already know the basics of linking different web documents together. Which includes html, css, php and javascript files. I also assume that you are already into jquery or javascript. And that you already know how to use php to fetch records from a mysql table.

Requirements

Procedure

Once you have gathered all the requirements and setup your testing/sandbox directory. Then you’re ready to get started.

First you have to include your database configuration:

<?php require_once('db_config.php'); ?>

Also include the jquery core file and the dataTables:

<script src="jq.js"></script>
<script src="data_tables/media/js/jquery.dataTables.js"></script>

You can find the dataTables javascript file under media/js  directory. There’s also the jquery core file in there so you don’t even have to download it separately.

Then, write the 1-liner code which will call up dataTables on your page. Note that the element that you should be selecting here should be the table that you are going to use. It would be better to use the id selector.

$('#dtable').dataTable();

Next, write the code which fetch records from the mysql database. In this tutorial, I’m using a library called ez sql. So you might find it different from the usual way of performing queries from php:

<?php
$list_names_n = $db->query("SELECT * FROM tbl_names");
$list_names = $db->get_results("SELECT * FROM tbl_names");
?>

You are free to modify the code if you are not using ez sql. But in case you want to learn how to use ez sql. Here’s a link to an article in catswhocode.com which introduces ez sql:

http://www.catswhocode.com/blog/php-fast-and-easy-sql-queries-using-ezsql

After that, write an if statement to check if the value returned by the query is greater than 0. Which means there is actually a result being returned.

<?php	if($list_names  > 0){ ?>
   //html table here
<? } ?>

Okay, I decided to divide this big pulp of code into two. So here’s the usual head section of the table. This is the portion which doesn’t repeat. So remember to place it outside the while loop, do-while loop, or foreach loop which loops through the results returned by the mysql query:

<div id="demo">
       <table border="1" id="dtable" class="display">
                  <thead>
	       <tr>
	       <th>idnum</th>
        	       <th>name</th>
	       </tr>
	  </thead>

Here’s the body section of the table. In which we loop through the results:

<tbody>
      <?php foreach($list_names  as $l){ ?>
	<tr>
	<td><?php echo $l->id_num; ?></td>
	<td><?php echo $l->names; ?></td>
	</tr>
      <?php } ?>
<tbody>
</table>
</div>

That’s it! Remember not to put where clause in the query. Because dataTables will do the job of searching for a specific record for you. All you have to do is to give it all the data that it needs to sort out.

Output

Here’s a clean result where the user hasn’t inputted anything yet. It also shows you a status as to how many entries/records are being shown. You can also control how many entries to show. Sort the results by either descending or ascending order in any of the columns by clicking the little arrow in the right-corner of each table header. And if you want to scroll through the results, you can click on the forward and back buttons in the bottom-right corner of the table.

image

And when there’s no match for the query inputted by the user:

image

As you can see, it can sort by any of those columns/fields shown in the table. In this case, it can sort by either name, or idnum:

image

Conclusion

I just showed you an easy way to paginate, search, and sort mysql results using dataTables. I hope you learned something. And thanks for reading. And follow me on twitter if you like: http://twitter.com/Wern_Ancheta

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s