Solved

Dynamic sort byclicking on column heading.

Posted on 2003-11-10
2
303 Views
Last Modified: 2013-12-24
OK, I have got this far (see code below).  What I want to do is be able to click on the column headings and have the Query add a "sort by" clause.

I assume I could do this by creating a form, then having vbscript (I don't use Javascript, hence I would use vbScript, but javascript in this exampe is completely fine) add the value to a form field, submit is and put that value in the query (or something ?? Haven't thought it out).

But... I figured there has to be an easier way?  Will Dreamweaver insert this code for you?  (I would like to know how to do it either way,)

I can write the code in .asp, but I am having a difficult time figurung this out with CF, partially because I am caching the query..

Well I think I made it clear, behind all the rambling, I just want to be ble to click on a column title and have the query sort by that value.

Thanks...  Code is below.

- I remain.

***************************************************

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfparam name="PageNum_AllListings" default="1">

<cfquery name="AllListings" datasource="Home4SaleByMe" cachedwithin = "#CreateTimeSpan(0, 6, 0, 0)#">
SELECT * FROM user975947.Listing ORDER BY Listing.Address_State
</cfquery>

<cfset MaxRows_AllListings=10>
<cfset StartRow_AllListings=Min((PageNum_AllListings-1)*MaxRows_AllListings+1,Max(AllListings.RecordCount,1))>
<cfset EndRow_AllListings=Min(StartRow_AllListings+MaxRows_AllListings-1,AllListings.RecordCount)>
<cfset TotalPages_AllListings=Ceiling(AllListings.RecordCount/MaxRows_AllListings)>
<cfset QueryString_AllListings=Iif(CGI.QUERY_STRING NEQ "",DE("&"&CGI.QUERY_STRING),DE(""))>
<cfset tempPos=ListContainsNoCase(QueryString_AllListings,"PageNum_AllListings=","&")>
<cfif tempPos NEQ 0>
  <cfset QueryString_AllListings=ListDeleteAt(QueryString_AllListings,tempPos,"&")>
</cfif>

<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML xmlns="http://www.w3.org/1999/xhtml">
<HEAD>
<TITLE>Start</TITLE>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</HEAD>

<BODY>
<a href="<cfoutput>#CurrentPage#?PageNum_AllListings=1#QueryString_AllListings#</cfoutput>">< First</a> |
<a href="<cfoutput>#CurrentPage#?PageNum_AllListings=#Max(DecrementValue(PageNum_AllListings),1)##QueryString_AllListings#</cfoutput>"><< Previous</a> |
<a href="<cfoutput>#CurrentPage#?PageNum_AllListings=#Min(IncrementValue(PageNum_AllListings),TotalPages_AllListings)##QueryString_AllListings#</cfoutput>">Next >></a> |
<a href="<cfoutput>#CurrentPage#?PageNum_AllListings=#TotalPages_AllListings##QueryString_AllListings#</cfoutput>">Last ></a>
&nbsp;&nbsp;Total Listings: <cfoutput>#AllListings.RecordCount#</cfoutput>

<table cellpadding="8" cellspacing="0">
      <tr bgcolor="#E8E8E8">
            <td>Listing ID</td>
            <td>First Name</td>
            <td>Last Name</td>
            <td>City</td>
            <td>State</td>
            <td>Zip</td>
      </tr>
      <cfoutput query="AllListings" startrow="#StartRow_AllListings#" maxrows="#MaxRows_AllListings#">
      <tr>
            <td>#AllListings.Listing#</td>
            <td>#AllListings.First_Name#</td>
            <td>#AllListings.Last_name#</td>
            <td>#AllListings.Address_City#</td>
            <td>#AllListings.Address_State#</td>
            <td>#AllListings.Address_Zip#</td>
      </tr>
      </cfoutput>
</table>
</BODY>
</HTML>
0
Comment
Question by:turbosig
2 Comments
 
LVL 2

Accepted Solution

by:
jonnygo55 earned 250 total points
Comment Utility
i sometimes just use the heading names with url links...
<a href="#CurrentPage#?sortBy=first_Name>First Name</a>...

Then you could use your cached query and assuming you are using cold fusion 5 or Mx query that query like:
<cfquery datasource="query" name="allListingsSorted">
     Select * from AllListings
     Order by #url.SortBy#
</cfquery>

You'll probably also have to pass some of the other variables you are keeping track of like PageNum_AllListings

Hope that helps
0
 
LVL 2

Author Comment

by:turbosig
Comment Utility
Thats what I thought.

Thanks.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now