Sorting Database record results
Pan_OBrien
I have a cfm that outputs a table and populates it with records of the
database. Using the following query:
As you can see from the SQL, the query can read url parameters like such:
"index.cfm?eng=MC&month=4&year=2007&destino=EXTRANJERO&submit=Submit+Query" and
output the records that fit the criteria.
Now my problem is everytime I try to sort by any of this headers [b]<a
href="index.cfm?sort=#IIF(sort is 19, '20', '19')#">[/b] the server will forget
allt he other search parameters within the url.
How do I preserve the other url parameters before sorting?
Also, if someone could plz point me in the direction of another site with a
great CF community I would appreciate it.
Thanks
<cfquery name="list" datasource="telsamina">
SELECT
CODIGO,
LARGO,
ALTO,
ANCHO,
ROUND(LARGO*ANCHO*ALTO, 2) AS M3,
ROUND(M3*'2.65', 2) AS TM,
DATE_FORMAT(FECHENVIO, '%d %m %y') AS FECHENVIO,
DESTINO,
GUIA,
SUBSTRING(CODIGO, -8, 1) AS UBI,
SUBSTRING(CODIGO, -6, 2) AS ENG
FROM HOJA1
WHERE '1'='1'
<cfif IsDefined("URL.ENG")>
<cfif URL.ENG IS NOT "ALL">
AND SUBSTRING(CODIGO, -6, 2) = '#URL.ENG#'
</cfif>
</cfif>
<cfif IsDefined("URL.DATE")>
<cfif URL.MONTH IS NOT "ALL">
AND DATE_FORMAT(FECHENVIO, '%d') = #URL.DATE#
</cfif>
</cfif>
<cfif IsDefined("URL.MONTH")>
<cfif URL.MONTH IS NOT "ALL">
AND DATE_FORMAT(FECHENVIO, '%m') = #URL.MONTH#
</cfif>
</cfif>
<cfif IsDefined("URL.YEAR")>
<cfif URL.YEAR IS NOT "ALL">
AND YEAR(FECHENVIO) = #URL.YEAR#
</cfif>
</cfif>
<cfif IsDefined("URL.DESTINO")>
<cfif URL.DESTINO IS NOT "ALL">
AND DESTINO = '#URL.DESTINO#'
</cfif>
</cfif>
<cfif IsDefined("URL.GUIA")>
AND REPLACE(GUIA,'-','') = #URL.GUIA#
</cfif>
ORDER BY
<cfswitch expression="#sort#">
<cfcase value="01">CODIGO</cfcase>
<cfcase value="02">CODIGO desc</cfcase>
<cfcase value="03">UBI</cfcase>
<cfcase value="04">UBI desc</cfcase>
<cfcase value="05">ENG</cfcase>
<cfcase value="06">ENG desc</cfcase>
<cfcase value="07">LARGO</cfcase>
<cfcase value="08">LARGO desc</cfcase>
<cfcase value="09">ANCHO</cfcase>
<cfcase value="10">ANCHO desc</cfcase>
<cfcase value="11">ALTO</cfcase>
<cfcase value="12">ALTO desc</cfcase>
<cfcase value="13">M3</cfcase>
<cfcase value="14">M3 desc</cfcase>
<cfcase value="15">FECHENVIO</cfcase>
<cfcase value="16">FECHENVIO desc</cfcase>
<cfcase value="17">DESTINO</cfcase>
<cfcase value="18">DESTINO desc</cfcase>
<cfcase value="19">GUIA</cfcase>
<cfcase value="20">GUIA desc</cfcase>
<cfcase value="21">TM</cfcase>
<cfcase value="22">TM desc</cfcase>
</cfswitch>
</cfquery>
<tr bgcolor="#CCCCCC">
<cfoutput>
<cfif list.RecordCount eq 0> <!--- //There aren't any records if RecordCount
is 0 ---> <BR>No hay Bloques en la database con esa criteria</cfif>
<td>No.</td>
<td><a href="index.cfm?sort=#IIF(sort is 01, '02', '01')#">CODIGO</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 03, '04', '03')#">ZONA</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 05, '06', '05')#">CONTRATISTA</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 07, '08', '07')#">LARGO</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 09, '10', '09')#">ANCHO</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 11, '12', '11')#">ALTO</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 13, '14', '13')#">M3</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 21, '22', '21')#">TM</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 15, '16', '15')#">ENVIO</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 17, '18', '17')#">DESTINO</a></td>
<td><a href="index.cfm?sort=#IIF(sort is 19, '20', '19')#">GUIA</a></td>
</cfoutput>
</tr>
cf_dev2
and grab all of the parameters except "sort". Then append those parameters to
your header. Possibly something like this
<cfset newQueryString = reReplaceNoCase(CGI_QUERY_STRING, "&*sort=[^&]*", "",
"all")>
<a href="index.cfm?sort=#IIF(sort is 19, '20', '19')#&#newQueryString#"> ..
Btw, I would recommend using cfqueryparam. Its dangerous to use URL variables
directly in a query.