Skip to main content
A
Anonymous
September 15, 2011
Question

data ordering question

  • September 15, 2011
  • 2 replies
  • 442 views

hello all, basicaly, im trying 2 order a query with the 'order by' statement in sql (Access) and then output it into a table. what im doing is: the table is queried, and outputted to an html table. then the column headers contain a hyperlink that sets the querystring to a value for ordering. im not sure what the problem is, but i guess i dont know how to structure everything so that the querystring is evaluated and orders the sql code. like if you could tell me how to setup the table, links, and query that would be nice. Thanks!

    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    September 15, 2011

    Try a different approach.  Once you have your data in an html table, use javascript to sort it.  There are lots of code samples on the internet to show you how it's done.

    -
    -__cfSearching__-
    Inspiring
    September 15, 2011

    im not sure what the problem is, but i guess i

    dont know how to structure everything so that the

    querystring is evaluated and orders the sql code.

    Can you post your code?

    A
    Anonymous
    September 15, 2011

    <!--- of is the order field --->

    <cfif #of# eq "">

    <!---there is no orderfield--->

    <cfquery name="qCustomers" datasource="customers">

    select id,[first name] as firstname, company, [last name] as lastname,[job title] as jobtitle, [Business Phone] as businessphone, [Fax Number] as faxnumber, Address, City, [state/province] as stateprovince,[Zip/Postal Code] as zippostal, [country/region]as countryregion, [web page] as webpage

    from Customers

    </cfquery>

    <cfelse>

    <!---there is an orderfield--->

    <cfif #of# eq "lastname">

    <cfset of="[last name]">

    </cfif>

    <cfif #of# eq "firstname">

    <cfset of="[first name]">

    </cfif>

    <cfif #of# eq "jobtitle">

    <cfset of="[job title]">

    </cfif>

    <cfquery name="qCustomers" datasource="customers">

    select id,[first name] as firstname, company, [last name] as lastname,[job title] as jobtitle, [Business Phone] as businessphone, [Fax Number] as faxnumber, Address, City, [state/province] as stateprovince,[Zip/Postal Code] as zippostal, [country/region] as countryregion, [web page] as webpage

    from Customers

    order by #of#

    </cfquery>

    </cfif>

    -
    -__cfSearching__-
    Inspiring
    September 15, 2011

    No need to duplicate the query.  Just define a default sort column in case a valid one was not specified.


    Just remember to scope your variables.  I took a guess that #of# is being passed in the URL scope.

    <cfquery name="qCustomers" datasource="customers">
           SELECT        id,[first name] as firstname, ....
           FROM           Customers
           ORDER BY    
          <cfswitch expression="#URL.of#">
                  <cfcase value="lastname">[last name]</cfcase>
                  <cfcase value="firstname">[first name]</cfcase>
                  <cfcase value="jobtitle">[job title]</cfcase>
                  <!--- if all else fails, sort by last name  --->
                  <cfdefaultcase>[lastname]</cfdefaultcase>
          </cfswitch>
    </cfquery>




    Message was edited by: -==cfSearching==-

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices