Table Sort / Page using AJAX

Download associated files (for working example): Download

Listing 1

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.
        <meta http-equiv="Content-Type" content=
"text/html; charset=iso-8859-1">
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">

            <h2>The Simpsons</h2>
            <div id=
"simpsons" style="width:300px;"></div>

            <div id=
"sales" style="width:300px;"></div>

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

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.
<!--- 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#

Step 3: Prepare the paging. See 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: ---->
<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">

    <table class="salesTable">

                <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">

                <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 class="link" onClick="ajax({url:'tblSales.cfm?order=ID Asc',fillDiv:'#url.divid#',showBusy:true})">ID </span>

                <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">
                <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 class="link" onClick="ajax({url:'tblSales.cfm?order=City Asc',fillDiv:'#url.divid#',showBusy:true})">City </span>

                <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">
                <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 class="link" onClick="ajax({url:'tblSales.cfm?order=Sales Asc',fillDiv:'#url.divid#',showBusy:true})">Sales ($K)</span>


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#">


Step 6: Set the paging numbers. See this tutorial for detailed explination of paging. 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.
    <td colspan="3" align="right">

    <cfloop index="Pages" from="0" to="#TotalPages#">     
        <cfset DisplayPgNo = Pages+1>
        <cfif URL.PageIndex eq pages>

            <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 DisplayPgNo LT TotalPages>
- </cfif>


Step 7: Close the table and the cfsavecontent and finally output the page.


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.

About This Tutorial
Author: Kris Brixon
Skill Level: Beginner 
Platforms Tested: CF5
Total Views: 112,523
Submission Date: August 13, 2006
Last Update Date: June 05, 2009
All Tutorials By This Autor: 4
Discuss This Tutorial
  • I had been thinking over a way to use AJAX to sort my tables, and when I finally got to where I had to figure it out, this tutorial made it easy.


Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.