Brad,
I don't have any code for you, but there are two things that I don't like about
the code that you have below:
1. price is varchar - which means that 10 < 2 ! (as it is sorted
alphabetically)
2. For you description criteria - you will meet all the first criteria OR
p.description ~* '#FORM.description#'
It should really be:
AND (p.productname ~* '#FORM.description#'
> OR p.description ~* '#FORM.description#')
Note the additional brackets.
With the additional brackets you may find that nothing works and you need to
work out what the real problem is!
Cheers,
Phil
Quoting Brad Apps <[EMAIL PROTECTED]>:
> 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
