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
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.
18 Responses
nice tutor, thanks. btw, how to implement it to wordpress page?
I mean I can make a sortable table in my wordpress page with it, but I can’t see the css style from jquery sortable.
This is probably a stupid question, but did you download one of the CSS files from the tablesorter website & have you attached it to your page?
If you have then the next thing to do would be to take the CSS stylings from the file and append them to the bottom of your WordPress themes CSS. Just make sure you target all the styles to a specific class if they aren’t already or all of your tables will be affected by it. This will make sure WP sees the CSS since it’s already reading that file for your template.
I hope that helps you a bit. If you have any more questions feel free to ask.
thanks for quick reply, sorry I am a novice coder. try to learn.
yes, I’ve inserted it’s css to my theme css, but it’s not effect at all.
it seems I have to learn more about css, it’s the code I’ve inserted to my css theme.
table.tablesorter { font-size: 12px; background-color: #4D4D4D; width: 1024px; border: 1px solid #000; } table.tablesorter th { text-align: left; padding: 5px; background-color: #6E6E6E; } table.tablesorter td { color: #FFF; padding: 5px; } table.tablesorter .even { background-color: #3D3D3D; } table.tablesorter .odd { background-color: #6E6E6E; } table.tablesorter .header { background-image: url(bg.png); background-repeat: no-repeat; border-left: 1px solid #FFF; border-right: 1px solid #000; border-top: 1px solid #FFF; padding-left: 30px; padding-top: 8px; height: auto; } table.tablesorter .headerSortUp { background-image: url(asc.png); background-repeat: no-repeat; } table.tablesorter .headerSortDown { background-image: url(desc.png); background-repeat: no-repeat; }so in the page I make a table with this code
Last Name First Name Email Due Web Site ....etcIt’s was sorted correctly, but the css style is not effect at all. any suggestion?
thanks.
Hmmm. Another stupid question, have you given the table the class tablesorter? Apart from that I can’t really see many problems with it.
If you could give the url to the page that the table is on I could have a look for you.
I do, it’s like
I did it in my localhost…
Sorry Kang. I honestly have no idea. The only other thing I can think of is using Firebug to make sure that the tablesorter class is being applied to the table correctly since it sounds like it isn’t for somereason.
If you can check with Firebug & see if it is overriding the tablesorter class with another one. If it is see if disabling the class that is overriding the tablesorter one will restore the table styling. It shouldn’t be happening but CSS sometimes has some strange quirks.
Sorry I can’t be of more help.
it’s ok, I solved the problem. anyway thanks for your responds.
No problem. Any chance you could share what the problem was just in case anyone else has a similar problem?
I’m using an template system wich means that i CANNOT generate html on my PHP file. In the HTML file i put some tags like {ID} {NAME} (and so on). And when my php reads the file, it will replace the tags to the current content, looks like the plugin does NOT work if i do this.
Anyone got the same problem?
Hi Alex,
I guess that depends how you get the values of {ID} in the first place. If you are able to the solution would be to figure out how to get the output of the code to be assigned to a template variable like {TABLE} or something. Again that all depends on how the template system you are using works. If you can explain that then I might beable to help you.
I need to prepare some stuff to show, cause the Template Engine I’m use is in brazillian-portuguese wich I’m almost sure you can’t read hehe.
Yep, good call.
It’s generally completely different per template system, so a look at how it assigns other template vars like {ID} would help. Although some systems are just use a simple
str_replacewhich means you’d just need to open the core files & add an extrastr_replacefor your info.If you can write some code, I’ll see what I can find for you.
Just beware that WordPress tends to have a bit of a gripe with PHP in the comments system. You could try using <pre lang=”php”> your code </pre> and hopefully that should work.
There is standard solution for template engines (like Smarty): how it knows what to parse and process? There is delimiters, they say to template engine where its piece starts and ends. Like {if blah-blah}, you can just define your own delimiters. For example you need to deal with CSS, so just define delimiters as {{ and }}…
Have you been able to get the pager add on to work as well? For some reason its not working for me…
I added the javascript at the top:
$(function() {
$(“#tablesorter-demo”)
.tablesorter({sortList:[[2,0]], widthFixed: true, widgets: ['zebra'], headers: {0: {sorter:false}, 5: {sorter:false}} })
.tablesorterPager({container: $(“#pager”)});
});
and then after the php table I have:
10
20
30
40
but nothing happens. Any idea what I am missing?
oops I forgot to comment out the code. Here it goes again -
$(function() { $("#tablesorter-demo") .tablesorter({sortList:[[2,0]], widthFixed: true, widgets: ['zebra'], headers: {0: {sorter:false}, 5: {sorter:false}} }) .tablesorterPager({container: $("#pager")}); });and then after the table:
Hi John,
I did manage to get it working. If you look on the tablesorter demo page for the pager you can see it working. As well as the jQuery code you need this code written where-ever you want the pager to appear:
The images are obviously images of your choice but those are examples. Give that a try and it should work. If not give this page a check, and if that still doesn’t help drop another comment & I’ll see if I can help further.
Hy but this table sorting function does not work if table is loaded using $.(“#tableDiv”).load(“loadTable.php”);
Hi,
Sorry but I don’t maintain the tablesorter jquery plugin, this tutorial was just built to show how to set it up as the tablesorter website doesn’t give a detailed tutorial. I can only imagine it is due to the javascript not being triggered after the ajax load. You could try adding a callback after the load is triggered & fire off the tablesorter by calling it directly.