I think you might need some parentheses to group your search fields
correctly. I've also rewritten the query to use the more explict join
syntax, hope you don't mind, but try this:
SELECT tblMusicProducts.musicprodID
, tblMusicProducts.musicprodname
, tblMusicProducts.musicprodprice
, tblMusicProducts.musicproddescription
, tblMusicProducts.musicprodimagethumb
, tblMusicProdTypes_X.musicprodID
, tblMusicProdTypes_X.musicprodtypeID
, tblMusicProdTypes.musicprodtypeID
, tblMusicProdTypes.musicprodtypename
FROM tblMusicProducts INNER JOIN tblMusicProdTypes_X
ON tblMusicProducts.musicprodID = tblMusicProdTypes_X.musicprodID
INNER JOIN tblMusicProdTypes
ON tblMusicProdTypes.musicprodtypeID =
tblMusicProdTypes_X.musicprodtypeID
WHERE tblMusicProdTypes_X.musicprodtypeID = #FORM.prodType#
<cfif Len(FORM.criteria)>
AND
(tblMusicProducts.musicprodname LIKE '%#FORM.criteria#%'OR
tblMusicProducts.musicproddescription LIKE '%#FORM.criteria#%')
</cfif>
The important part being that you want the musicprodtypeID match AND
(either musicprodname OR musicproddescription )
Hope that helps,
Judah
Will Tomlinson wrote:
> I have a search form that's looking for text in a title or description. But
> there's also a select menu to filter by a product type.
>
> My problem is, I can't get that product type filter workin in the query,
> unless you don't enter any search criteria.
>
> Go here: http://208.106.220.143/index.cfm
>
> Enter "test" in the text input. I'm dumping everything so you can see what
> it's doing. It's still showing all the product types even tho I have this in
> the query:
> WHERE tblMusicProdTypes_X.musicprodtypeID =
> #FORM.prodType#
>
> Here's the whole query. Don't worry about the cfqueryparams, I'm just trying
> to get this to work first.
>
> SELECT tblMusicProducts.musicprodID, tblMusicProducts.musicprodname,
> tblMusicProducts.musicprodprice, tblMusicProducts.musicproddescription,
> tblMusicProducts.musicprodimagethumb, tblMusicProdTypes_X.musicprodID,
> tblMusicProdTypes_X.musicprodtypeID, tblMusicProdTypes.musicprodtypeID,
> tblMusicProdTypes.musicprodtypename
> FROM tblMusicProducts, tblMusicProdTypes_X, tblMusicProdTypes
> WHERE tblMusicProdTypes_X.musicprodtypeID =
> #FORM.prodType#
> <cfif Len(FORM.criteria)>
> AND tblMusicProducts.musicprodname LIKE '%#FORM.criteria#%'
> OR tblMusicProducts.musicproddescription LIKE '%#FORM.criteria#%'
> </cfif>
> AND tblMusicProducts.musicprodID = tblMusicProdTypes_X.musicprodID
> AND tblMusicProdTypes.musicprodtypeID = tblMusicProdTypes_X.musicprodtypeID
> ORDER BY #order#
>
> Thanks,
> Will
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7.
Free Trial. http://www.adobe.com/products/coldfusion
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:271824
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
