|
Displaying and sorting/paging tabular data using the JQuery tablesorter plugin, and query objects |
||||||||
One of the more repetitive tasks a server side developer encounters is displaying the results from a query. This is traditionally in the format of a table that displays the rows of data, along with any other functionality, such as paging controls and sortable headers.
I was recently commissioned to look into building a generic table display "engine", and thought I'd investigate if there were any JQuery plugins that could do the bulk of the work for me. Ideally I didn't want to have to write a whole load of script to parse sorting variables, and detect if a limit was set on the returned record set for paging.
After some investigation I ended out using the table sorter JQuery plugin http://tablesorter.com/docs/.
This plugin allows for sortable results that you can page through, and it does not keep posting the values back and forth to the server.
Start by including the references to the JQuery libraries. I've also included references to the paging plugin, and the blue theme stylesheet.
2
3<s/cript type="text/javascript" src="/path/to/jquery.tablesorter.js"></script>
4
5<s/cript type="text/javascript" src="/path/to/ /jquery.tablesorter.pager.js"></script>
6
7<link rel="stylesheet" href="css/blue.css" type="text/css" />
Next we will create a fake query, so that we have some records to display.
2<cfset variables.qOptions = QueryNew( "id, name, color" ) />
3
4<cfset QueryAddRow( variables.qOptions ) />
5<cfset variables.qOptions[ "id" ][ variables.qOptions.RecordCount ] = "1" />
6<cfset variables.qOptions[ "name" ][ variables.qOptions.RecordCount ] = "Value 1" />
7<cfset variables.qOptions[ "color" ][ variables.qOptions.RecordCount ] = "Red" />
8
9<cfset QueryAddRow( variables.qOptions ) />
10<cfset variables.qOptions[ "id" ][ variables.qOptions.RecordCount ] = "2" />
11<cfset variables.qOptions[ "name" ][ variables.qOptions.RecordCount ] = "Value 2" />
12<cfset variables.qOptions[ "color" ][ variables.qOptions.RecordCount ] = "Green" />
13
14<cfset QueryAddRow( variables.qOptions ) />
15<cfset variables.qOptions[ "id" ][ variables.qOptions.RecordCount ] = "3" />
16<cfset variables.qOptions[ "name" ][ variables.qOptions.RecordCount ] = "Value 3" />
17<cfset variables.qOptions[ "color" ][ variables.qOptions.RecordCount ] = "Blue" />
18
19<cfset QueryAddRow( variables.qOptions ) />
20<cfset variables.qOptions[ "id" ][ variables.qOptions.RecordCount ] = "4" />
21<cfset variables.qOptions[ "name" ][ variables.qOptions.RecordCount ] = "Value 4" />
22<cfset variables.qOptions[ "color" ][ variables.qOptions.RecordCount ] = "White" />
We have to change the way we build the table code slightly, as the JQuery plugin is expecting certain field naming conventions. Give your table a class name of tablesorter. This is the style that the JQuery is watching for.
2 <thead>
3 <tr>
4 <cfoutput>
5 <cfloop list="#variables.qOptions.columnlist#" delimiters="," index="variables.index">
6 <th>#variables.index#</th>
7 </cfloop>
8 </cfoutput>
9 </tr>
10 </thead>
In the code above I am looping over the columnlist of the query to generate headings. I've had to change the headings to 'th' tags which I never normally use.
Next we can generate the table content, making sure it is inside a 'tbody' html tag. Simply loop over the query displaying all the results within td tags.
2 <cfoutput query="variables.qOptions">
3 <tr>
4 <td>#variables.qOptions.id#</td>
5 <td>#variables.qOptions.name#</td>
6 <td>#variables.qOptions.color#</td>
7 </tr>
8 </cfoutput>
9 </tbody>
10</table>
Lastly the pager plugin is looking for a div with a class of pager. Inside this div you place your paging controls, and the value of the page recordsets that you want to offset by.
2 <form>
3 <img src="addons/pager/icons/first.png" class="first"/>
4 <img src="addons/pager/icons/prev.png" class="prev"/>
5 <input type="text" class="pagedisplay"/>
6 <img src="addons/pager/icons/next.png" class="next"/>
7 <img src="addons/pager/icons/last.png" class="last"/>
8 <select class="pagesize">
9 <option selected="selected" value="10">10</option>
10
11 <option value="20">20</option>
12 <option value="30">30</option>
13 <option value="40">40</option>
14 </select>
15 </form>
16</div>
This builds a one page table display that can paginate and sort with a single refresh.
A full example of this is here.
Your website is really cool and this is a great inspiring article. Thank you so much.
Thanks for sharing the info, keep up the good work going.... I really enjoyed exploring your site. good resource...
