Skip to main content
M
MissJaneSC
Participant
October 29, 2009
Question

Coldfusion Query ORDER BY syntax

  • October 29, 2009
  • 2 replies
  • 620 views

Hi,

I have products stored in different categories and I want to run a query that will display the products either by product name or by product price depending on the category ID.  I have it working with just one category being sorted by product name (as below)  but how do I specify for multiple categories e.g. IF #URL.cat# EQ "10" OR "11" OR "5" OR "14" ???

<cfif #URL.cat# EQ "10">
<cfquery name="catalogue" datasource="#request.SiteDSN#" username="#request.DSNUser#" password="#request.DSNPasswd#">
SELECT *
FROM products
WHERE prod_cat_id = <cfqueryPARAM value = "#URL.cat#"
   CFSQLType = "CF_SQL_INTEGER"> ORDER BY prod_name
</cfquery>
<cfelse>
<cfquery name="catalogue" datasource="#request.SiteDSN#" username="#request.DSNUser#" password="#request.DSNPasswd#">
SELECT *
FROM products
WHERE prod_cat_id = <cfqueryPARAM value = "#URL.cat#"
   CFSQLType = "CF_SQL_INTEGER"> ORDER BY prod_price
</cfquery>
</cfif>

Thanks in advance!

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    October 29, 2009

    My example goes for economy, writing the query just once.

    <cfswitch expression="#URL.cat#">

    <!--- categories 5, 10 or 12 --->
    <cfcase value="5,10,12">
            <cfset orderByClause="prod_name">
        </cfcase>

    <!--- categories 11 or 14 --->
        <cfcase value="11,14">
            <cfset orderByClause="prod_price">
        </cfcase>

    <!--- all categories other than 5,10,11,12,14 --->
        <cfdefaultcase>
            <cfset orderByClause="prod_name">
        </cfdefaultcase>
    </cfswitch>

    <cfquery name="catalogue" datasource="#request.SiteDSN#" username="#request.DSNUser#" password="#request.DSNPasswd#">
    SELECT *
    FROM products
    WHERE prod_cat_id = <cfqueryPARAM value = "#URL.cat#" CFSQLType = "CF_SQL_INTEGER">

    ORDER BY #orderByClause#
    </cfquery>

    S
    sunshinerc
    Participant
    October 29, 2009

    I would do one query with the IF statement in the order by:

    <cfquery name="catalogue" datasource="#request.SiteDSN#" username="#request.DSNUser#" password="#request.DSNPasswd#">
    SELECT *
    FROM products
    WHERE prod_cat_id = <cfqueryPARAM value =     "#URL.cat#"
       CFSQLType =     "CF_SQL_INTEGER"> 
    ORDER BY <cfif ListFind("5,10,11,14",URL.cat)>prod_name<cfelse>prod_price</cfif>
    </cfquery>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices