Sort Link

[NetworkGuy101]

I have setup a page to output info and let the user sort by a linked column name It doesnt seem to be working correctly though. Help is appreciated- Joe


<cfparam name=&quot;sort&quot; default=&quot;1&quot;>

<cfquery name=&quot;Userview&quot; datasource=&quot;IGNEW&quot;>
SELECT *
FROM Userinfo
ORDER BY
<cfswitch expression=&quot;#sort#&quot;>

<cfcase value=&quot;1&quot;>memberid</cfcase>
<cfcase value=&quot;2&quot;>badgenumber</cfcase>
<cfcase value=&quot;3&quot;>datetrained</cfcase>
<cfcase value=&quot;4&quot;>badgenumber </cfcase>
<cfcase value=&quot;5&quot;>DEROS</cfcase>


</cfswitch>
</cfquery>

<table width=&quot;974&quot; border=&quot;0&quot;>

<tr>
<td><a href=&quot;eetuserview.cfm?sort=1&quot;>Member ID</a></td>
<td width=&quot;115&quot;><font color=&quot;#0000FF&quot; size=&quot;2&quot;>Last Name</font></td>
<td width=&quot;117&quot;><font color=&quot;#0000FF&quot; size=&quot;2&quot;>First Name</font></td>
<td><a href=&quot;eetuserview.cfm?sort=2&quot;>Badge Number</a></td>
<td width=&quot;89&quot;><font color=&quot;#0000FF&quot; size=&quot;2&quot;>Unit</font></td>
<td width=&quot;127&quot;><font color=&quot;#0000FF&quot; size=&quot;2&quot;>Duty Phone</font></td>
<td><a href=&quot;eetuserview.cfm?sort=3&quot;>DEROS</a></td>
<td><a href=&quot;eetuserview.cfm?sort=4&quot;>Date Trained</a></td>
<td width=&quot;253&quot;><font color=&quot;#0000FF&quot; size=&quot;2&quot;>EETUSER Name</font></td>
</tr>
<cfoutput query=&quot;Userview&quot;>
<tr bordercolor=&quot;##000000&quot;>
<td bordercolor=&quot;##000000&quot;><font size=&quot;2&quot;>#Userview.memberid#</font></td>
<td bordercolor=&quot;##000000&quot;><font size=&quot;2&quot;>#Userview.lastname#</font></td>
<td bordercolor=&quot;##000000&quot;><font size=&quot;2&quot;>#Userview.firstname#</font></td>
<td><font size=&quot;2&quot;>#Userview.badgenumber#</font></td>
<td><font size=&quot;2&quot;>#Userview.unit#</font></td>
<td><font size=&quot;2&quot;>#Userview.dutyphone#</font></td>
<td><font size=&quot;2&quot;>#LSDateFormat(Userview.Deros,'M/DD/YYYY')#</font></td>
<td><font size=&quot;2&quot;>#Userview.Datetrained#</font></td>
<td><font size=&quot;2&quot;>#Userview.EETUSER#</font></td>
</tr>
</cfoutput>
</table>

 

[r937]

where does #sort# get its value from? i mean, other than the CFPARAM, which sets it to 1, which means the query will return rows in memberid sequence

if there's a form field called sort, the CFPARAM actually doesn't touch it, because it defaults to the variables scope

and since <cfswitch expression=&quot;#sort#&quot;> doesn't have a scope, it defaults to variables scope as well

another example of why one should always scope one's variables


rudy

http://r937.com/

 

[NetworkGuy101]

Maybe if I passed it through the URL and had sort equal URL? Number?

 

[r937]

you could

my preference would be a dropdown list, or a set of radio buttons, i.e. the sort field would come over as a form field

 

[MorganGreylock]

I did something similar to this with URL parameters.
One of the options was to sort by location, so I did this:

<cfif isdefined(&quot;URL.LOCATION&quot;)>
  <cfquery name=&quot;view_store&quot; datasource=#DSN#>
    select * from table
    where store_city = '#URL.LOCATION#'
    order by store_city, lastname
  </cfquery>
</cfif>

Hope that helps,
MG

 

[Demon148]

<cfparam name=&quot;URL.sortBy&quot; default=&quot;ID&quot;>

This Swaps ASC with Desc

<cfif isdefined(&quot;URL.sortByType&quot>
<cfif #url.sortByType# eq 'DESC'>
<cfset sort = 'DESC'>
<cfelse>
<cfset sort = 'ASC'>
</cfif>
<cfelse>
<cfset sort = 'ASC'>
</cfif>

<cfquery datasource=&quot;source&quot; name=&quot;name&quot;>
Select *
From Table
Order By <cfif isDefined (&quot;url.sortBy&quot>#sortBy# #sort#<cfelse>ID</cfif>
</cfquery>

Example A Column Header &quot;id&quot;
Each time link is clicked changes Asc to desc
Besure the links don't have any spaces between the CFIF's

<td><a href=&quot;YourPage.cfm?sortBy=id
<cfif URL.sortBy EQ &quot;id&quot;>
<cfif #sort# eq 'ASC'>&sortByType=DESC
<cfelse>&sortByType=ASC</cfif>
</cfif>>ID</a>

*Optional --This is just some graphics to show user the sort
<cfif #sort# eq 'DESC' and url.sortby eq &quot;id&quot;>
<img src=&quot;down_icon.gif&quot;>
<cfelseif #sort# eq 'ASC' and url.sortby eq &quot;id&quot;>
<img src=&quot;up_icon.gif&quot;>
</cfif>
*
</td>

Hope this Helps