Sort data

Report · Feb 09, 2010

I know I'm missing something so simple... but I'm too close to the subject so hopefully someone here can pin-point where the mistake is.

Really appreciate any input. Thanks.

I have a page the users make certain selections from via checkboxes which is then passed to the results page.

Here is sample code from my initial selection page:

<tr>

<p align="left"><b><font size="2" color="#003466" face="Arial">Apartment Size</font></b></td>

</tr>

<tr>

<b><font face="Arial" size="2">Select All</font></b></td>

</tr>

<tr>

<td valign="top" align="left" width="13%"><font size="2" face="Arial">Studio</font></td>

</tr>

<tr>

<td valign="top" align="left" width="13%"><font size="2" face="Arial">1 Bedroom</font></td>

</tr>

<tr>

<td valign="top" align="left" width="13%"><font size="2" face="Arial">2 Bedroom</font></td>

</tr>

<tr>

<td valign="top" align="left" width="13%"><font size="2" face="Arial">3 Bedroom+</font></td>

</tr>

</table>

Here is my results page, what's happening is if a user selects "Studio" they may only get 4 results but when they click the header to sort the address for instance, they end up with entire queried list... so it loses the "Studio" parameter somewhere...You will see more parameters here on this page then the previous selection page above, just assume those pass the same way...

<cfparam name = "session.lastCol" default = "Building.Bldg_address">

<cfscript>
    function changeOrder(colName){
         /*If this is the first time the page is loaded we have to be sure to change the default column to "DESC". Otherwise the first time a user clicks the link to sort it the other direction nothing will happen.*/
        if(not isdefined("url.sortCol")){
            /*If the default column name and the name passed to the function are the same set the next sort order to "DESC"*/
            if(session.lastCol eq colName){
                newOrder = "DESC";
            }else{
                newOrder = "ASC";
            }
            /*return the query string that will sort the query. In this example the return may look like sortCol=Lname&sortOrder=DESC*/
            return "sortCol=" & colName & "&sortOrder=" & newOrder;
        }else{
            /*Check to see if the user is clicking on the same column to change the sort order.*/
            if((session.lastCol eq url.sortCol) AND (colName eq url.sortCol)){
                /*change the sort order in the link*/
                if(url.sortOrder eq "ASC"){
                    newOrder = "DESC";
                }else{
                    newOrder = "ASC";
                }
                return "sortCol=" & colName & "&sortOrder=" & newOrder;
            }else{
                /*if the user clicked a different heading to sort by we must change the last sort column held in the session variable and change the next sort order to DESC if the column is not a clicked column the sort order will go back to ASC*/
                if(colName eq url.sortCol){
                    session.lastCol = colName;
                    newOrder = "DESC";
                }else{
                    newOrder = "ASC";
                }
                return "sortCol=" & colName & "&sortOrder=" & newOrder;
            }
        }
    }
</cfscript>

<cfquery>
SELECT Building.BuildingID, Building.Bldg_area, Building.Bldg_Address, Building.Bldg_Address, master_list.ID, master_list.unit, master_list.BR, master_list.Bath, master_list.rent_amt, master_list.avail, tbl_neighborhood.Bldg_neighborhood, master_list.display, Bldg_Info.Bldg_Phone
FROM ((Building INNER JOIN tbl_neighborhood ON Building.Bldg_area = tbl_neighborhood.Bldg_area) INNER JOIN master_list ON Building.BuildingID = master_list.BuildingID) INNER JOIN Bldg_Info ON Building.BuildingID = Bldg_Info.BuildingID
WHERE master_list.display = 1
        <cfif (#Bldg_area# NEQ 12)>AND Building.Bldg_area IN (#Bldg_area#)</cfif>
        <CFIF (#bldg_type# NEQ 12)>AND master_list.apt_type IN (#bldg_type#)</CFIF>
        <CFIF (#bldg_price# EQ 'A')>AND master_list.rent_amt > '0'<cfelse>AND (master_list.rent_amt >= '#bldg_price5#' AND master_list.rent_amt <= '#bldg_price4#')</CFIF>
ORDER BY <cfif isdefined("url.sortCol")>
                    #url.sortCol# #url.sortOrder#
                <cfelse>
                    #session.lastCol# ASC
                </cfif>
</cfquery>

<table border="0" cellpadding="0" style="border-collapse: collapse" width="100%" id="table2" bordercolor="#FFFFFF">
          <tr>
            <td>
            <cfoutput><a href="search_result_SORT.cfm?#changeOrder('Bldg_address')#">Address</a></cfoutput>   </td>
            <td>
            <cfoutput><a href="search_result_SORT.cfm?#changeOrder('Building.Bldg_area')#">Neighborhood</a></cfoutput></td>
            <td>
            <cfoutput><a href="search_result.cfm?#changeOrder('master_list.BR')#">Apartment Size</a></cfoutput></td>
            <td><cfoutput><a href="search_result_SORT.cfm?#changeOrder('master_list.rent_amt')#">Price</a></cfoutput></td>
            <td>Contact #</td>
          </tr>
          <cfif AptSearch.recordcount eq 0>
          <cfoutput>
          <tr>
            <td>No apartments available for your search criteria, please try again.</td>
          </tr>
          </cfoutput>
          </cfif>

          <cfoutput query="AptSearch">
          <cfset aptID = #ID#>
          <tr>
            <td>#Bldg_address#</td>
            <td>#Bldg_neighborhood#</td>
            <td>#BR#/#Bath#</td>
            <td>$#rent_amt#</td>
            <td>#Bldg_Phone#</td>
          </tr>

</cfoutput>
</table>

Report · Feb 10, 2010

If you display your results in a flash grid the columns will be sortable. If you want to stick with html, using js to sort the existing query results will make the initial form selections irrelevent and will also process faster.

Adobe Community

Sort data