Solved

Coldfusion Column Sorting2

Posted on 2001-09-07
4
198 Views
Last Modified: 2013-12-24
I asked this question before...  snakehollywoods answer to my question works, I just found out it doesn't work on my page, that you have to be logged into though, for some reason.  I can get it to work on any other page though except when I have a page protected by this login.  Does anyone else know the answer to the question below, other than a form method?  or a fix? Below is my question 1st, than snakehollywoods answer.

*********

I want to know how to sort the columns of a query.  I know how to do it via the SQL statement.  What
I need is for a user to be able to pull up the webpage with query and click on the column headings of
the table to sort it by that column(asc AND desc would be great...).  For example...  Lets say I have
a table called "music", and I have fields: ID, Artist, Title, Description, and etc.  I want the user
to be able to click on ID to sort the table by ID and if they click on Artist, sort by artist, and etc.
 If there is anyone who can help me on this, it would be greatly appreciated.  


Accepted Answer
From: snakehollywood Date: 09/07/2001 08:18AM PST
Text Below
Question History
Accepted Answer
From: snakehollywood  Date: 09/07/2001 08:18AM PST  
Put buttons into your table headers that will act as the sort button.
When the button is clicked on, it will pass the name of the column to sort into a hidden form field,
and then submit the form back to the same page.

e.g

<td><input type="submit" name="sortorder" value="Name" onclick="document.forms[0].orderby.value='name
ASC'"></td>

this puts a button into your column header, which when clicke don, will pass the value "name ASC" into
the hidden form field called orderby.

At the top of the page you simply check for the existance of the form button, if it exists then apply
a dynamic where clause using the hidden formfield.

e.g

<cfquery>
select * from table
<cfif isdefined('form.sortorder')>
order by #form.orderby#
</cfif>
</cfquery>

this will order your query by the column "name" in ascending order.

Now you just create buttons for each column you wish to sort. You can use image buttons for ASC and
DESC options.
 
0
Comment
Question by:bptacek
  • 3
4 Comments
 
LVL 1

Accepted Solution

by:
Soulive earned 190 total points
ID: 6465311
on each colum heading you make a hyperlink that has a URL variable that travels with it dictating what the query should sort on.  So if your page is displayrecords.cfm and it has columns Artist Titl and Description.  You would make those column titles into links.
<a href="displayrecords.cfm?sort=artist>Artist</a>
<a href="displayrecords.cfm?sort=title>Title</a>
<a href="displayrecords.cfm?sort=label>Record Label</a>

make sure to param the url vars to make them present so you don't have to test for their existance.
<cfparam name="url.sort" default="id">

So your query looks like this.
select *
from music
where 0=0
order by #url.artist#
-------------------------------------------------------
If you want to put int the ascending/descending feature in there, you could make seperate links for each with the command attached in the url variable.  Since ascending is a default you would have only DESC as a variable.
<a href="displayrecords.cfm?sort=artist>Artist Ascending</a>
<a href="displayrecords.cfm?sort=artist&order=desc>Artist</a>

Set your variables up
<cfparam name="url.sort" default="id">
<cfparam name="url.order" default="">

query looks like this

select *
from music
where 0=0
order by #url.artist# #url.order#

HOPE THIS IS WHAT YOU WERE LOOKING FOR
0
 
LVL 1

Expert Comment

by:Soulive
ID: 6465321
CORRECTION in the first query example:
url.sort not url.artist
0
 

Author Comment

by:bptacek
ID: 6465371
Works perfect!  Thanks!
0
 
LVL 1

Expert Comment

by:Soulive
ID: 6465426
here is another neat trick you can do too.  Make your column headings images with up and down arrows.  For instance, Artist (up arrow image) and Artist (down arrow image) and then just Artist with no arrow. Call them artistup.gif and artistdown.gif and artistplain.gif.

First make your params and your cfsets.

<cfparam name="url.sort" default="id">
<cfparam name="url.order" default="">

<cfif url.order is "" and url.sort is "artist">
  <cfset url.order="DESC">
  <cfset artistimage="artistdown"
<cfelseif url.order is "DESC" and url.sort is "artist">
  <cfset url.order="">
  <cfset artistimage="artistup"
<cfelseif url.sort neq "artist">
  <cfset url.order="">
  <cfset artistimage="artistplain"
</cfif>


Now on your url you have:

<a href="displayrecords.cfm?sort=artist?order=#url.order#">

Then right after the opening anchor tag you put this:

<img src="#variables.artistimage#.gif">

Then close the link with the </a>

So now each time they click the artist images it will have the appropriate image there to tell you what the colum is doing.  Sort up, sort down, or sorting on something besides artist.

In the future you may even consider sorting on more than one field with the use of cookies or session variables.
0

Featured Post

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Fix A 403 error 13 117
Has my website been infiltrated? 21 69
Systems talking to each other 5 142
exchange 2010 turning off 3des ciphers 2 319
In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

820 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