Brad,
If you are storing the "price" as a varchar, then you will not be able
to use mathematical operators such as <= or >= on it cos it is a string.
The trick with that if you cannot convert the price field to int is to
"convert" the strings to integers by using "cast" in the select
statement.
Also, to search the product name and description fields use "like" in
the last bit:
<cfquery name="results" datasource="#request.ds#">
select *, CAST(products.price AS SMALLINT) AS int_price
from products p, subcategories sc, categories c
WHERE p.fksubcategoryid = sc.subcategoryid
AND sc.fkcategoryid = c.categoryid
<!--- search through subcategories --->
<cfif isdefined("FORM.subcategory") and
(FORM.subcategory gt 0)>
and p.fksubcategoryid = #FORM.subcategory#
</cfif>
<!--- search for from price range --->
<cfif form.fromprice is not "">
and int_price >= #FORM.fromprice#
</cfif>
<!--- search for to price range --->
<cfif form.toprice is not "">
and int_price <= #FORM.toprice#
</cfif>
<!--- search through various columns / tables for
description --->
<cfif isdefined("FORM.description")>
AND p.productname LIKE '%#FORM.description#%'
OR p.description LIKE '%#FORM.description#%'
</cfif>
order by p.productname
</cfquery>
This is untested and I can see where it might fall down, but it might
put you on the right track.
Good luck,
Steve c
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Brad Apps
Sent: Friday, 2 April 2004 3:06 PM
To: CFAussie Mailing List
Subject: [cfaussie] Advanced Search Code...
Hey All,
I am unfortunately following up on a job and trying to debug some
advanced
search code which doesn't seem to be working. Very frustrating!!!
Just wondering if anyone could share some advanced search code, ie. a
form
and action pages which could search through product names, description,
price ranges, product categories, etc.
The code which I am working with is as follows, when entering in price
ranges it outputs nothing, but entering a description only works fine.
The database is postgres, price, description and name fields are varchar
and fksubcatercory field is int4:
<cfquery name="results" datasource="#request.ds#">
select *
from products p, subcategories sc, categories c
WHERE p.fksubcategoryid = sc.subcategoryid
AND sc.fkcategoryid = c.categoryid
<!--- search through subcategories --->
<cfif isdefined("FORM.subcategory") and
(FORM.subcategory gt 0)>
and p.fksubcategoryid = #FORM.subcategory#
</cfif>
<!--- search for from price range --->
<cfif form.fromprice is not "">
and p.price >= '#FORM.fromprice#'
</cfif>
<!--- search for to price range --->
<cfif form.toprice is not "">
and p.price <= '#FORM.toprice#'
</cfif>
<!--- search through various columns / tables for
description --->
<cfif isdefined("FORM.description")>
AND p.productname ~* '#FORM.description#'
OR p.description ~* '#FORM.description#'
</cfif>
order by p.productname
</cfquery>
Help would be greatly appreciated, some better code to start over with
may
be better.
Thanks.
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
