Solved

Dynamic sort byclicking on column heading.

Posted on 2003-11-10
2
307 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
ID: 9716890
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
ID: 9718813
Thats what I thought.

Thanks.
0

Featured Post

Free camera licenses with purchase of My Cloud NAS

Milestone Arcus software is compatible with thousands of industry-leading cameras for added flexibility. Upon installation on your My Cloud NAS, you will receive two (2) camera licenses already enabled in the software. And for a limited time, get additional camera licenses FREE.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

15 Experts available now in Live!

Get 1:1 Help Now