[DFW CFUG] MySQL WHERE clause with precedence

[Mike Sumner]

I am working on a search page and have this query to handle multiple conditions in the Where clause, one of which is searching for a partial (LIKE) .  It seems like I can not pass the order of precedence with () for the AND   (This OR That) AND OU=U.  I only get back the matches for the "This" part.  I am using NAVICAT and tested the query against the MySQL 4._ DB and it works there, I tried a scaled down version using Dreamweaver (OK I know I should be using Eclipse, but not at the moment).  It there a procedure to pass the "()" see below...

 

 

If anyone can help I would appreciate it.

 

Portion of query in question...

 

...

`longitems`.`OU`,
`longitems`.`DatePosted`,
`member_roster_table`.`member_name`
FROM `longitems`
Inner Join `member_roster_table` ON `longitems`.`MemberNumber` = `member_roster_table`.`member_number`
WHERE
 1=1

 

...

 

<cfif ARGUMENTS.key NEQ "">
AND
 (`longitems`.`ProductDesc` LIKE <cfqueryparam value="%#ARGUMENTS.key#%" cfsqltype="cf_sql_clob">
OR
 `longitems`.`BrandName` = <cfqueryparam value="%#ARGUMENTS.key#%" cfsqltype="cf_sql_clob">)
</cfif>
AND
  `longitems`.`OU` = <cfqueryparam value="#ARGUMENTS.OU#" cfsqltype="cf_sql_clob">
ORDER BY `longitems`.`DatePosted` DESC
</cfquery>

 

 

 

Entire query

 

<cfquery name="getosearch" datasource="nbs4">
SELECT `longitems`.`ID`,
`longitems`.`MemberNumber`,
`longitems`.`UserID`,
`longitems`.`BrandName`,
`longitems`.`ProductDesc`,
`longitems`.`Model`,
`longitems`.`Color`,
`longitems`.`Size`,
`longitems`.`WholesaleCost`,
`longitems`.`RetailPrice`,
`longitems`.`QuantityAvailable`,
`longitems`.`Contact`,
`longitems`.`Phone`,
`longitems`.`Fax`,
`longitems`.`Email`,
`longitems`.`Hunting`,
`longitems`.`Camping`,
`longitems`.`Fishing`,
`longitems`.`RetailAthletic`,
`longitems`.`Team`,
`longitems`.`OU`,
`longitems`.`DatePosted`,
`member_roster_table`.`member_name`
FROM `longitems`
Inner Join `member_roster_table` ON `longitems`.`MemberNumber` = `member_roster_table`.`member_number`
WHERE
 1=1

 

 <cfif ARGUMENTS.Hunting EQ "TRUE">
AND
 `longitems`.`Hunting` = <cfqueryparam value="#ARGUMENTS.Hunting#" cfsqltype="cf_sql_tinyint">
</cfif>
<cfif ARGUMENTS.Fishing EQ "TRUE">
AND
 `longitems`.`Fishing` = <cfqueryparam value="#ARGUMENTS.Fishing#" cfsqltype="cf_sql_tinyint">
</cfif>
<cfif ARGUMENTS.Camping EQ "TRUE">
AND
 `longitems`.`Camping` = <cfqueryparam value="#ARGUMENTS.Camping#" cfsqltype="cf_sql_tinyint">
</cfif>
<cfif ARGUMENTS.RetailAthletic EQ "TRUE">
AND
 `longitems`.`RetailAthletic` = <cfqueryparam value="#ARGUMENTS.RetailAthletic#" cfsqltype="cf_sql_tinyint">
</cfif>
<cfif ARGUMENTS.Team EQ "TRUE">
AND
 `longitems`.`Team` = <cfqueryparam value="#ARGUMENTS.Team#" cfsqltype="cf_sql_tinyint">
</cfif>
<cfif ARGUMENTS.key NEQ "">
AND
 (`longitems`.`ProductDesc` LIKE <cfqueryparam value="%#ARGUMENTS.key#%" cfsqltype="cf_sql_clob">
OR
 `longitems`.`BrandName` = <cfqueryparam value="%#ARGUMENTS.key#%" cfsqltype="cf_sql_clob">)
</cfif>
AND
  `longitems`.`OU` = <cfqueryparam value="#ARGUMENTS.OU#" cfsqltype="cf_sql_clob">
ORDER BY `longitems`.`DatePosted` DESC
</cfquery>

 

Mike Sumner

Nations Best Sports

817-788-0034 ext 244

817-788-8542 Fax

 

_______________________________________________
Reply to DFWCFUG: 
  List@list.dfwcfug.org
Subscribe/Unsubscribe: 
  http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives: 
    http://www.mail-archive.com/list%40list.dfwcfug.org/             
  http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors: 
  www.HostMySite.com 
  www.teksystems.com/