sorting in coldfusion

[danarashad]

I output a list of records and want to be able to sort them.
What I basically am after is to be able to have an <input> (or even better have a <select> with 1 to "number of records") next to each record and be able to enter a number that sets the sort order.
here's the code i have.

<!-- beging order section -->

<cfif isDefined("form.updateShowLevels")>
<CFQUERY name="cats" datasource="#datasource#">
SELECT rec_id, status
FROM categories
WHERE lvlID = #lvlID# and featureID=2
</CFQUERY>
<CFSET tempCatID=#cats.rec_id#>
<cfquery name="tempResources" datasource="#datasource#">
SELECT rec_id, title, description, showlevel, cat_id, document FROM documents
WHERE lvlID=#lvlID# and cat_id=#tempCatID#
and subCatID = 0
</cfquery>

<cfloop query="tempResources">
<CFSET objLevelID="form.showlevel#tempCatID#">
<CFSET form.showlevel=Evaluate(#objLevelID#)>
<cfset tempID=tempResources.rec_id>
<cfset myStatus=0>

<!---At least one active checkbox is checked--->
<!--- <CFIF ISDEFINED("thisStatus")><CFLOOP list="#thisStatus#" index="statusX">

<CFIF tempID IS #trim(statusX)#>
<CFSET myStatus =1>
<cfbreak>
</CFIF>
</CFLOOP>
</CFIF> --->
<cfquery name="update" datasource="#datasource#">
Update categories
SET showLevel=#form.showLevel#
WHERE rec_id=#tempID#
</cfquery>
</CFLOOP> --->
</CFIF>



<!-- end order section -->




<CFINCLUDE template="_action.cfm">
<cfquery name="sNameQRY" datasource="#datasource#">
select sName from labelTitles
where levelID=#lvlID#</cfquery>

<CFQUERY name="cats" datasource="#datasource#">
SELECT rec_id, cat, showLevel, sortBy
FROM categories
WHERE lvlID=#lvlID# AND featureID=2
ORDER BY showLevel, cat
</CFQUERY>
<cfquery name="subCat" datasource="#datasource#">
select rec_id, cat, catID, sortBy
from subcats
WHERE lvlID=#lvlID# AND featureID=2
ORDER BY showLevel, cat</cfquery>

<CFQUERY name="res" datasource="#datasource#">
SELECT rec_id, title, description, document, cat_id, lvlID, subCatID, sortDate
FROM documents
WHERE lvlID=#lvlID#
</CFQUERY>



<CFINCLUDE template="_action.cfm">
<CFIF cats.recordCount IS NOT 0>
<hr>
<form name="form2" method="post" action="index.cfm?sec=<cfoutput>#sec#&lvlID=#lvlID#</cfoutput>" >
<table width="99%" border="0">
<cfoutput query="cats">
<CFSET tempCatID=#cats.rec_id#>
<!---this finds the records with the current rec_id--->
<cfquery name="tempResources" datasource="#datasource#">
SELECT rec_id, title, description, showlevel, document FROM documents
WHERE lvlID=#lvlID# and cat_id=#tempCatID#
and subCatID = 0
order by <cfif cats.sortBy is 3>sortDate DESC<cfelseif cats.sortBy is 2>sortDate<cfelse>title</cfif>
</cfquery>
<cfquery name="tempSubCat" dbtype="query">
select rec_id, cat from subcat where catID=#tempCatID#
</cfquery>
<tr>
<td valign="bottom">
<a href="index.cfm?sec=#sec#&lvlID=#lvlID#<cfif not isdefined('detail') or (isdefined('detail') and detail is not tempCatID)>&detail=#tempCatID#</cfif>">
<cfif not isdefined('detail') or (isdefined('detail') and detail is not tempCatID)>
<img src="#isiPath#/arrow1.gif" border="0">
<cfelse>
<img src="#isiPath#/arrow2.gif" border="0">
</cfif>
<b><font color="black">#cats.cat#</font></b></a> </td>
</tr>
<TR>
<TD valign="top"> <cfif isdefined('detail') and detail is tempCatID>
<TABLE border="1" width="100%">
<!--- This brings back the links for each category if they exist--->
<CFIF tempResources.recordCount IS NOT 0>
<CFLOOP query="tempResources">
<CFSET tempRecid=#tempResources.rec_id#>

<tr>
<td width="5%" nowrap valign="top">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="10%" valign="top" nowrap><a href="##" onClick="option#rec_id#();return false"><img src="#iSIpath#/DELETE.gif" width="16" height="16" border="0" alt="Delete"></a>
<a href="index.cfm?sec=#sec#&lvlID=#lvlID#&act2=2&id=#rec_id#"><img src="#iSIpath#/EDIT.gif" width="20" height="20" border="0" alt="Edit"></a></td>
<td>
<select name="showLevel#tempCatID#" class-"backMenu">
<cfloop index="loopCount" from="1" to="#tempresources.recordCount#">
<option value="#loopCount#" <cfif loopcount is showLevel>selected</cfif>>#loopCount#</option>
</cfloop>
</select></td>
</tr>
</table></td>
<td valign="top"> <font size="2"> <a href="#webPath#/#sNameQRY.sName#/documents/#tempResources.document#" target="_blank" onClick="self.blur()">#tempResources.title#</a> </font>- <font size="2"><i>#tempResources.description#</i></font> <div align="center"></div></td>
</tr>
</CFLOOP>
<CFELSE>
<tr>
<td colspan="2"> No documents exists for this category.</td>
</tr>
</CFIF>
</table>
<cfquery name="fred" datasource="#datasource#">
select rec_id, cat, sortBy from subcats where catID=#tempCatID#
</cfquery>
<table width="100%" border="0">
<cfloop query="fred">
<cfset tempSubCatID=#fred.rec_id#>
<cfset tempSubCat=#fred.cat#>
<cfquery name="tempResources1" datasource="#datasource#">
SELECT rec_id, title, description, showlevel document FROM documents
WHERE lvlID=#lvlID# and subCatID=#tempSubCatID#
order by <cfif fred.sortBy is 3>sortDate DESC<cfelseif fred.sortBy is 2>sortDate<cfelse>title</cfif>
</cfquery>
<tr>
<td width="2%"><img src="#isiPath#/space.gif" width="20" height="9"></td>
<td><a href="index.cfm?sec=#sec#&lvlID=#lvlID#&detail=#tempCatID#<cfif not isdefined('detail1') or (isdefined('detail1') and detail1 is not tempSubCatID)>&detail1=#tempSubCatID#</cfif>">
<cfif not isdefined('detail1') or (isdefined('detail1') and detail1 is not tempSubCatID)>
<img src="#isiPath#/arrow1.gif" border="0">
<cfelse>
<img src="#isiPath#/arrow2.gif" border="0">
</cfif>
<b><font color="black">#tempSubCat#</font></b></a></td>
</tr>
<cfif tempResources1.recordCount IS 0 and isdefined('detail1') and detail1 is tempSubCatID>
<tr>
<td colspan="2"><div align="center">No
Documents exist for #tempSubCat#</div></td>
</tr>
</cfif>
<cfif tempResources1.recordCount GT 0 and isdefined('detail1') and detail1 is tempSubCatID>
<tr>
<td>&nbsp;</td>
<td>
<TABLE border="1" width="100%">
<!--- This brings back the links for each category if they exist--->
<CFIF tempResources1.recordCount IS NOT 0>
<CFLOOP query="tempResources1">
<CFSET tempRecid=#tempResources1.rec_id#>
<cfset temptitle='#tempResources1.title#'>
<cfset tempDesc='#tempResources1.description#'>
<cfset tempG='#tempResources1.document#'>


<tr>
<td width="8%" nowrap valign="top">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="5%" valign="top" nowrap><a href="##" onClick="option#rec_id#();return false"><img src="#iSIpath#/DELETE.gif" width="16" height="16" border="0" alt="Delete"></a>
<a href="index.cfm?sec=#sec#&lvlID=#lvlID#&act2=2&id=#rec_id#"><img src="#iSIpath#/EDIT.gif" width="20" height="20" border="0" alt="Edit"></a></td>
</tr>
</table></td>
<td valign="top"> <font size="2"> <a href="#webPath#/#sNameQRY.sName#/documents/#tempG#" target="_blank" onClick="self.blur()">#temptitle#</a> </font>- <font size="2"><i>#tempDesc#</i></font> <div align="center"></div></td>
</tr>
</CFLOOP>
</CFIF>
</table></td>
</tr>
</cfif>
</cfloop>
<tr>
<td nowrap colspan="3"> <div align="center">
<input type="submit" name="Submit2" value="Update">
<input type="hidden" name="updateShowLevels" value="1">
</div></td>
</tr>
</table>

 

[r937]

wow

does that work?

r937.com | rudy.ca

 

[danarashad]

no, i am not sure what i am messing. can anyone help??

 

[imstillatwork]

framework zealots would have a field day with this one.

Simplicity says make the input a text input and allow only numbers. end users don't always get what to do though.

the select dropdown has tons of problems with long lists, refreshing after every selection, etc.

there are some good javascripts out there that allow drag and drop re-arranging or records, but you need to know that users run js...

for small lists, i sometimes use up and down arrows that move the position with just a click. long lists this gets annoying..



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!