Coldfusion Query ORDER BY syntax

[MissJaneSC]

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!

[sunshinerc]

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>

[BKBK]

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>