So a few people have contacted me asking me how to create a table that sorts without contacting the MySQL server again. Well it’s simple, I use a extremely powerful jQuery plugin called TableSorter 2.0 made for jQuery by Christian Bach. The hard part is integrating it into your data which is probably generated by [...]

So a few people have contacted me asking me how to create a table that sorts without contacting the MySQL server again. Well it’s simple, I use a extremely powerful jQuery plugin called TableSorter 2.0 made for jQuery by Christian Bach. The hard part is integrating it into your data which is probably generated by PHP via a MySQL server. So here’s how to tie the two together.

Step One: jQuery & TableSorter 2.0

So first you need to get jQuery & TableSorter 2.0. Then in a PHP file add the two via a script tag. It should look something like this:

<script src="jquery.pack.js" type="text/javascript"></script>
<script src="jquery.tablesorter.pack.js" type="text/javascript"></script>

Got that? Good. Let’s move on.

Step Two: Creating The Table

In the body of your PHP page open & close a PHP block. This is where we are going to work. I’m going to give you the script all in one go & then explain each couple of lines as we go.

<?php

$db = new mysqli("server", "user", "pass", "database");

$query = "SELECT name, occupation, age FROM ajaxdelete LIMIT 0,10";

$result = $db->query($query, MYSQLI_STORE_RESULT);

$o = '<table id="myTable"><thead><tr><th>Name</th><th>Occupation</th><th>Age</th></tr></thead><tbody>';

while(list($name, $occu, $age) = $result->fetch_row()) {
	$o .= '<tr><td>'.$name.'</td><td>'.$occu.'</td><td>'.$age.'</td></tr>';
}

$o .= '</tbody></table>';

echo $o;
?>

So here we go. I create a new instance of mysqli which I use in this tutorial. If your server doesn’t have mysqli compiled into PHP ask them to do it, or you can use the MySQL class I used to use. You can download that from the sidebar, or finally you can use the old fashioned way. I’m not going to cover that though.

$query = "SELECT name, occupation, age FROM ajaxdelete LIMIT 0,10";
$result = $db->query($query, MYSQLI_STORE_RESULT);

I create the query. This just gets three fields from a test table I have & limits it to 10 for the purposes of this tutorial. Then we retrieve the result. MYSQLI_STORE_RESULT tells mysqli to give all the results from the database at once. MYSQLI_USE_RESULT is used to stream a large result set from the server, as such all new queries will return an error until you close the previous result set.

$o = '<table id="myTable"><thead><tr><th>Name</th><th>Occupation</th><th>Age</th></tr></thead><tbody>';

Next we open our table & create our headers. Note the use of the <thead> & <tbody> tags, they must be used or the TableSorter plugin won't work. The ID must also be set, you can however change this.

while(list($name, $occu, $age) = $result->fetch_row()) {
	$o .= '<tr><td>'.$name.'</td><td>'.$occu.'</td><td>'.$age.'</td></tr>';
}
$o .= '</tbody></table>';
echo $o;

Finally we run through the database results. I use list here since it is an ideal way of handling the results sent back from the database. You can however use the traditional array by replacing list() with a variable. We then make our rows & cells using the variables provided by the list() function. We close our table body & table tags & then echo out our variable.

If you are wondering why we have been assigning to a variable rather than echoing, well it's because it makes our table manipulable after it has been created. It is also quicker. Ok so that's debaitable, but I've found it quicker.

Step Three: Initialize TableSorter

Now the final step is to make TableSorter sort the table. You do this by using this bit of code in your head, just underneath where you attached the TableSorter plugin.

$(document).ready(function() {
        $("#myTable").tablesorter({widgets: ['zebra']});
});

MySQLI, PHP, jQuery & TableSorter 2.0 all working in harmony

The widgets: ['zebra'] enables the built in function to add classes to table rows so that you can use CSS to zebra stripe the table. You can see it in action in the picture to the right. There are sample CSS files available on the TableSorter 2.0 website, you can use them to see what CSS rules are available to you.

That's it though. That is how to make a on-the-fly sortable table using Christian Bach's excellent jQuery plugin & MySQLI. I hope you liked this tutorial, if you have any questions or suggestions then drop me a comment.