Simple "Order By" - problem 2

[dv8er88]

I am am setting a sort order by a drop down menu. The default is by Horse Power and it works fine. The other option is to sort by Manufacturer; if they select this option I want to sort by Manufacturer name then in HP order.

But the data come out wrong the Manufacturer and HPs are mixed:
Example:
CUMMINS 300
CUMMINS 250
DETROIT 400
DETROIT 300
YAMMY 300
CAT 30
CAT 10
DETROIT 450
DETROIT 500

If I output the query it look fine. Here it is:
SELECT * FROM product WHERE product_type = '1' AND product_on_web = '1' Order BY 'product_manu, product_hp'

Here is the code
<cfif #form.sort# is 'HP'><cfset sort 'product_hp'></cfif>
<cfif #form.sort# is 'Manuf.'><cfset sort = 'product_manu, product_hp'></cfif>

<!--- Get --->
<CFQUERY Name="get" datasource="#var_datasource#" username="#var_username#" password="#var_password#">

SELECT * FROM product WHERE product_type = '1' AND product_on_web = '1' ORDER BY '#sort#'
</cfquery>

 

[FALCONSEYE]

this is from your first query
Order BY 'product_manu, product_hp'


and this is from your second query
ORDER BY '#sort#'

as you will realize, you don't try to sort by both, just 'either' 'or'

i would do something like


ORDER BY <cfif FORM.sort IS 'HP'>product_hp
<cfelse>
product_manu, product_hp
</cfif>

hope it helps.
you are also "overusing" # signs

 

[r937]

make sure there are no single quotes around the column names in the ORDER BY

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[dv8er88]

Thank you r937 that was the problem.