1 Reply Latest reply on Feb 10, 2010 5:53 AM by Dan Bracuk

    Sort data

    still_smiling

      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:

       

                  <table border="0" cellpadding="0" style="border-collapse: collapse" width="37%" id="table2" bordercolor="#FFFFFF" height="204">

                      <tr>

                          <td colspan="2">

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

                      </tr>

                      <tr>

                          <td valign="top" align="right" width="26%" colspan="2"> <p align="left">

                          <input type="checkbox" name="sall" onClick="checkSAll(document.mylist.bldg_type,this)" style="float: left">

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

                  </tr>

                     <tr>

                          <td valign="top" align="right" width="8%"><input type="checkbox" name="bldg_type" value="5"></td>

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

                      </tr>

                      <tr>

                          <td valign="top" align="right" width="8%"><input type="checkbox" name="bldg_type" value="1"></td>

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

                      </tr>

                      <tr>

                        <td valign="top" align="right" width="8%"><input type="checkbox" name="bldg_type" value="2"></td>

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

                      </tr>

                      <tr>

                          <td valign="top" align="right" width="8%"><input type="checkbox" name="bldg_type" value="3"></td>

                          <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="sort" default="master_list.apt_type, Building.Bldg_address, master_list.rent_amt,   building.bldg_area">
      <cfparam name="sortorder" default="ASC">

       


      <!--- set the default column to sort by. --->
      <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></cfout put></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>

        • 1. Re: Sort data
          Dan Bracuk Level 5

          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.