Table Sort / Page using AJAX
Download associated files (for working example): Download Table.zip
Overview
Using Ajax you can simplify This table sort and paging technique is similar to using an iframe, but uses AJAX instead.
The main page has a div to hold the table and a call to fill the page. The fill page is no different than a normal display page with paging and sorting, but you are able to disconnect the main page and the table page. This becomes usefull since you do not clutter up the main url. A secondary effect is this creates a two part page and gives the illusion that the page loads faster.
Main Page:
Step 1: Include the ajax.js file. This is a generic wrapper for xmlHttpRequest. Include any stylesheets for the tables.
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Table Sort / Page</title>
<script type="text/javascript" src="ajax.js"> </script> (ajax.js explained)
<link rel="stylesheet" type="text/css" href="/includes/style.css" />
Step 2:Include a Div on the page and give it an ID. This is the container we will use to fill the table with when it is called.
<table cellspacing="9">
<tr>
<td>
<h2>The Simpsons</h2>
<div id="simpsons" style="width:300px;"></div>
</td>
<td>
<h2>Sales</h2>
<div id="sales" style="width:300px;"></div>
</td>
</tr>
</table>
Step 3: Add an ajax call to the bottom of the body or on the body onload event to fill the div. This call says it wants to call the page tblSales.cfm and insert the results into the Div with the Id of "sales" and also show a generic processing message while we are waiting for the page to load.
<script type="text/javascript">
// inital load
ajax({url:'tblSimpsons.cfm',fillDiv:'simpsons',showBusy:true});
ajax({url:'tblSales.cfm',fillDiv:'sales',showBusy:true});
</script>
Data Page (tblSales.cfm):
Step 1: First, whitespace in AJAX pages seem to cause more trouble than normal pages, so we will wrap all processing around cfsilent so we can eliminate needless whitespace. Second, set the default for the Div Id we are communicating with.
<cfsilent>
<!--- control the whitespace --->
<cfparam name="url.divid" default="sales">
Step 2: Set the default sort order so we can always treat the SQL query the same. Then query the data.
<!--- Default the Sort Order --->
<cfparam name="url.order" default="City Desc">
<!--- Query Database --->
<cfquery name="qryGetSales" dbtype="query">
SELECT Id, City, Sales
FROM myTable
ORDER BY #url.order#
</cfquery>
Step 3: Prepare the paging. See http://tutorial310.easycfm.com/ for a detailed explanation.
1. Set the default for the page we are on.
2. Set the number of records we want to show per page.
3. Find the total number of pages available.
4. Calculate which row to start on.
5. Calculate which row to end on.
<!--- Paging example from: http://tutorial310.easycfm.com/ ---->
<cfparam name="URL.PageIndex" default="0">
<cfset RecordsPerPage = 4>
<cfset TotalPages = (qryGetSales.Recordcount/RecordsPerPage)-1>
<cfset StartRow = (URL.PageIndex*RecordsPerPage)+1>
<cfset EndRow = StartRow+RecordsPerPage-1>
Step 4: First, use cfsavecontent to hold the output. Since we are using cfsilent, we need to collect all the output until we close the cfsilent tags. Second, build the header row of the table. For each sortable field, we need to check and see how/if the field is already sorted so we know which direction to key the sort command. Third, add an AJAX call to refill the table using the value in the querystring. The call is just like the call on the main page except we are specifying a new sort order. Note: We do not need to include paging information since we want to go back to page 1 when the sort order is changed.
<cfsavecontent variable="output">
<cfoutput>
<table class="salesTable">
<tr>
<th>
<cfif url.order EQ "ID Desc">
<span class="link" onClick="ajax({url:'tblSales.cfm?order=ID Asc',fillDiv:'#url.divid#',showBusy:true})">
ID <img src="arrow_down.gif">
</span>
<cfelseif url.order EQ "ID Asc">
<span class="link" onClick="ajax({url:'tblSales.cfm?order=ID Desc',fillDiv:'#url.divid#',showBusy:true})">
ID <img src="arrow_up.gif">
</span>
<cfelse>
<span class="link" onClick="ajax({url:'tblSales.cfm?order=ID Asc',fillDiv:'#url.divid#',showBusy:true})">ID </span>
</cfif>
</th>
<th>
<cfif url.order EQ "City Desc">
<span class="link" onClick="ajax({url:'tblSales.cfm?order=City Asc',fillDiv:'#url.divid#',showBusy:true})">City
<img src="arrow_down.gif">
</span>
<cfelseif url.order EQ "City Asc">
<span class="link" onClick="ajax({url:'tblSales.cfm?order=City Desc',fillDiv:'#url.divid#',showBusy:true})">
City <img src="arrow_up.gif">
</span>
<cfelse>
<span class="link" onClick="ajax({url:'tblSales.cfm?order=City Asc',fillDiv:'#url.divid#',showBusy:true})">City </span>
</cfif>
</th>
<th>
<cfif url.order EQ "Sales Desc">
<span class="link" onClick="ajax({url:'tblSales.cfm?order=Sales Asc',fillDiv:'#url.divid#',showBusy:true})">
Sales ($K) <img src="arrow_down.gif">
</span>
<cfelseif url.order EQ "Sales Asc">
<span class="link" onClick="ajax({url:'tblSales.cfm?order=Sales Desc',fillDiv:'#url.divid#',showBusy:true})">
Sales ($K) <img src="arrow_up.gif">
</span>
<cfelse>
<span class="link" onClick="ajax({url:'tblSales.cfm?order=Sales Asc',fillDiv:'#url.divid#',showBusy:true})">Sales ($K)</span>
</cfif>
</th>
</tr>
</cfoutput>
Step 5: Fill the table with the proper rows. Use the cfoutput tag and the values calculated above to limit the rows that are displayed.
<cfoutput query="qryGetSales" startrow="#StartRow#" maxrows="#RecordsPerPage#">
<tr>
<td>#qryGetSales.Id#</td>
<td>#qryGetSales.City#</td>
<td>#qryGetSales.Sales#</td>
</tr>
</cfoutput>
Step 6: Set the paging numbers. See this tutorial for detailed explination of paging. http://tutorial43.easycfm.com/ The most important thing to note is for each page we need to do another AJAX call, just like above, but we need include the paging information and the sorting information since we want to maintain the sort order between pages.
<tr>
<td colspan="3" align="right">
<cfoutput>
<cfloop index="Pages" from="0" to="#TotalPages#">
<cfset DisplayPgNo = Pages+1>
<cfif URL.PageIndex eq pages>
<strong>#DisplayPgNo#</strong>
<cfelse>
<span class="link" title="Go to Page #DisplayPgNo#" onClick="ajax({url:'tblSales.cfm?order=#url.order#&PageIndex=#Pages#',fillDiv:'#url.divid#',showBusy:true})">#DisplayPgNo#</span>
</cfif>
<cfif DisplayPgNo LT TotalPages> - </cfif>
</cfloop>
</cfoutput>
</td>
</tr>
Step 7: Close the table and the cfsavecontent and finally output the page.
</table>
</cfsavecontent>
</cfsilent><cfoutput>#output#</cfoutput>
Step 8: Follow the same steps for tblSimpsons.cfm as you did for tblSales.cfm. The sorting and paging will not increment the back button in the browser and the two tables are isolated from each other so the URL in one does not need to know anything about the other table. If they were using a normal page the URL querystring could get very messy dealing with both tables on the same page.
-
Building Excel Spreadsheets via XML
Building Excel spreadsheets using Microsofts SpreadsheetML XML dialect.
Author: Kris Brixon
Views: 26,668
Posted Date: Tuesday, January 24, 2006 -
Simple Suggest List using Scriptaculous
This tutorial shows the suggest list (auto complete) idea using the built in functions in Scriptaculous with a CF data page. Working example at: http://www.brixontech.com/examples/webtwo/autocomplete/index.cfm
Author: Kris Brixon
Views: 20,957
Posted Date: Sunday, July 30, 2006 -
Task Scheduler without the Administrator
See link for tutorial and files: http://www.brixontech.com/examples/scheduler/index.cfm The scheduled task manager in the ColdFusion Administrator can be replicated outside of the administrator. You can add, update, run, and delete scheduled tasks from a tag and you can list all tasks using the unsupported ServiceFactory class.
Author: Kris Brixon
Views: 17,923
Posted Date: Friday, August 11, 2006 -
Easy Table Sort / Page using AJAX
Table sort and paging using AJAX calls to fill a div that will be the container for the table. See the following page for a live demo. http://www.brixontech.com/examples/table/index.cfm
Author: Kris Brixon
Views: 27,817
Posted Date: Sunday, August 13, 2006
