Well its been a long weekend and I’m starting to wrap it up. But I couldn’t go to bed without redoing the documentation for my baby the MooTools TableSorter and updating the demo page. The documentation will loosely follow what you see in the demo page.
HTML Setup
You’ll need to include 3 files: MooTools 1.2 or higher, the CSS file, and the Table Sorter javascript class. You’ll also want to make sure all the images are there and update the CSS image paths to jive with the location on your web server.
1 2 3 | <link rel="stylesheet" media="screen" href="/mootools/table-sorter/table-sorter.css" /> <script type="text/javascript" src="/js/mootools1-2.js"></script> <script type="text/javascript" src="/mootools/table-sorter/table-sorter.js"></script> |
PHP Setup
You’ll want to use the TableSorter php class I included to make things easy one you.
Notice setting the table headers is important. For instance when a user clicks on city the TableSorter will send the ORDER BY stored in the TH Title. This is how your server side code will know what to order by. Reply to this post if you have any questions on the server-side PHP code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | include_once 'TableSorter.class.php'; function returnGeoHtmlTableStr($whereClause='',$startingFromRecord=0,$rowsPerPage=100,$orderBy='city ASC') { mysql_connect('localhost','user','password','database'); mysql_select_db('database'); $startingFromRecord = (int) $startingFromRecord; $rowsPerPage = (int) $rowsPerPage; $sql = "SELECT count(*) as count FROM cnizz_geo $whereClause "; $result = mysql_query($sql); $countArr = mysql_fetch_assoc($result); $totalRows = $countArr['count']; if($orderBy==''){ $orderBy='city ASC'; } $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM cnizz_geo $whereClause ORDER BY $orderBy LIMIT $startingFromRecord,$rowsPerPage "; $result = mysql_query($sql); $arr = array(); while($row=mysql_fetch_assoc($result)){ $arr[]=$row; } $sql = "SELECT FOUND_ROWS() as totalRows"; $result = mysql_query($sql); $foundArr=mysql_fetch_assoc($result); $str = ' <table class="DefaultTable" style="width:500px;"> '.TableSorter::returnMetaStr($startingFromRecord,$rowsPerPage,$foundArr['totalRows'],count($arr),$orderBy).' <tr style="background:#FFF;"><td colspan="10" style="height:5px;"> </td></tr> <tr id="GeoHead" class="DefaultTableHeader"> <th id="City" title="'.(($orderBy=='city ASC')?'city DESC':'city ASC').'">City</th> <th id="State" title="'.(($orderBy=='state ASC')?'state DESC':'state ASC').'">State</th> <th id="Abbrev" title="'.(($orderBy=='state_abbrev ASC')?'state_abbrev DESC':'state_abbrev ASC').'">State</th> <th id="County" title="'.(($orderBy=='county ASC')?'county DESC':'county ASC').'">County</th> </tr> '; $x=1; foreach($arr as $i) { if($x%2){ $class=''; } else{ $class='zebra'; } $str.= '<tr class="'.$class.'"> <td><a id="'.$i['city'].'" title="'.stripslashes($i['city_id']).'">'.stripslashes($i['city']).'</a></td> <td>'.$i['state'].'</td> <td>'.$i['state_abbrev'].'</td> <td>'.$i['county'].'</td> </tr>'; $x++; } $str.= '<tr style="background:#FFF;"><td colspan="10" style="height:5px;"> </td></tr>'; $str.=$meta.'</table>'; return $str; } |
Your Server Side Controller
1 2 3 | if($_GET['action']=='returnGeoHtmlTableStr'){ die(returnGeoHtmlTableStr('',$_GET['start'],$_GET['rows'],$_GET['orderBy'])); } |
Now the JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | window.addEvent('domready',function(){ sorter = new TableSorter({ request: 'action', action: 'returnGeoHtmlTableStr', destination: 'XhrDump', prev: 'PagePrev', next: 'PageNext', head: 'GeoHead', rows: 100, defaultStartEndWaitEnabled: 1, startWait: '', endWait: '' }); }) |
Lets explain this line by line.
- request: ‘action’, This is how you name the GET parameter. So in this instance the GET looks like $_GET['action']
- action: ‘returnGeoHtmlTableStr’, This is the value of GET parameter defined in step 1, so $_GET['action'] = ‘returnGeoHtmlTableStr’
- destination: ‘XhrDump’, This is the element id where TableSorter will write results too
- prev: ‘PagePrev’, This is the element that the class will listen on for Previous Page requests.
- next: ‘PageNext’, Same as above except it goes to the next page when this element is clicked
- head: ‘GeoHead’, This tells the class where to look for all those TH tags, which it uses to do sorts on if a Title is defined on the TH tag.
- rows: 100, How many rows should the request return.
- page: location.href, Where to send the GET request
- method: ‘get’, The type of method (GET or POST). By default the method is get, but you can change it to post.
- defaultStartEndWaitEnabled: ’1′, this is set to 1 by default. Setting this to 0 disables the built-in visuals (fades, image etc.) you see when performing a sort.
- startWait: ”, empty by default, will do a callback function only if defaultStartEndWaitEnabled is set to 0. This is for doing your own custom visuals.
- endWait: ”, empty by default, will do a callback function only if defaultStartEndWaitEnabled is set to 0.
Other cool stuff you can do
You can add additional parameters to the GET request in the form of parameter name => parameter value.
1 | sorter.addParameter('zipcode','84101'); |
You can remove these user-defined parameters by calling removeAllParameters.
1 | sorter.removeAllParameters(); |
You can remove a specific parameter by calling removeParameter.
1 | sorter.removeParameter('zipcode'); |
You can initiate a sort anytime you want by calling sort.
1 | sorter.sort(); |
There are some known issues in IE where the up/down arrows will not appear on sorts. Also the visual-effects are a bit degraded in IE as well. When posting questions or bugs you can use the HTML pre tag and set language=”javascript” to get syntax highlighting (wp-syntax plugin). Just makes things easier for me and other readers. Take a look at the source on my syntax highlighting and you’ll see what I mean.
Please reply to this blog post with any comments, bugs, or questions. Enjoy.
No related posts.
Tags: mootools, mootools table sorter
Looks like the demo page is broken! Thanks for the tutorial.
Thanks for letting me know. I moved hosts last night and looks like I forgot my mootools directory. Oops.
Hello,
I have a problem with this plugin.
I join all needed files :
after that I have :
…
and I get JS error in table-sorter.js on line 54:
$(this.options.head) is null
var tr = $(this.options.head).getParent();
I tried 0.9.5 version and 0.9.6 version too
Hi Otto. In what browser are you experiencing this problem?
It was in last firefox, but I found where was the problem. I have bad some of this ID :
So Now its OK.
But I have problem with anything else :
When I use some sort, arrow shows before the name, after next click it shows arrow with the oposite direction.
I thought it should be according to parameter in title (for example “name ASC” arrow up and “name DESC” arrow down).
Next “cosmtetic problem is, that I have in header some row with colspan=”2″. If I ORDER BY next on right row, it will highlight one of the row before…
Maybe I dont wrote this clear enough. So when I get to my working PC a send can send you an image with this problem.
If your script cant solve this. I could try it and send you…
I may have gotten the arrows a bit backwards. That can be fixed in my next release. I never designed this with a colspan in my mind as its main use was for strictly tabular data right out of a database. Can you include some screen shots so I can see what its doing. Thanks.
[...] for reporting bugs and feature requests for the new version. For documentation please refer to the version 0.9.5 post. This documentation is still valid. 0.9.6 just fixes bugs in Internet Explorer 6 and Internet [...]
The problem with colspan is in this :
cell[cellIndex].addClass(‘focusedColumn’);
cell[cellIndex].addClass(‘focusedColumn’);
cellIndex is 2 (because first row has colspan=”2″) and it should be 3
I solve this for my actual application, but it isnt universal.
But while I using your script I foun another thing
I would like to set some callback function, when sort is done..
In table I have link to delete one record, but if I sort table, events disapear, because current table is deleted and new created…
Is there any way, how set callback ? something like “onSortFinish”…
That is a good feature request. There should really be a onSortComplete and onSortStart. For events that must be duplicated across multiple elements I usually just put an onclick in the returned data server side, but I will add the functionality you request for next release.
Its good to hear that
I try to solve it for now, but I look forward to next release.
Hi Chris,
Nice table sorter you’ve got. I got one question. In your demo the page doens’t scroll back to the top, but to the top of your menu. I downloaded your files but my demo scrolls back to the top of the page.
You know what can be wrong?
Thanks in advance.
Cheers
Hmm..I assume you mean when you perform a sort it’s not scrolling back to where you want it (like in the demo). It’s possible the files I included from the demo on github download are not up to date. Have you integrated table sorter into your application? Does it work as expected there?
first of all, great thanks for this script. It looks great.
but i had a problem when i tried to follow your exemple.
There was a error:
web developer (firefox) sayd “syntax error” on “var TableSorter = new Class({” in “table-sorter.js”.
after a lot of test, i found the problem, in “table-sorter.js” you start with
/*
/*
—
description: table sorter
license: MIT-style
………………………………
provides: [table sorting]
*/
*/
And there is a comment syntax that close nothing.
you should put
/*
—
description: table sorter
license: MIT-style
……………………………..
provides: [table sorting]
*/
PS: Sorry for my English, but my mother language is french
Thanks sainsi and your english is just fine. I’ll get that fixed asap.
Thanks for your work, I use it in an administration panel.
Is there anyway to have also first and last page link?
Regards
Thanks. That would be easy to do I just have not developed on this plugin in a while, but I’d like to do another release this year and thats a great idea.