Simple query problem involving Boolean logic.

[Mavent]

I'm building a query that searches by database and returns matching (or almost matching) terms.  That part isn't the problem- I have it up and working.  The problem is that I'm trying to narrow down the search results, and it's not working.

 

Here's the query that works:

$result = mysql_query("SELECT * FROM auctions WHERE  name LIKE '%".$searchterm."%' OR Address LIKE '%".$searchterm."%' OR state like '%".$searchterm."%'");

 

Here's the query that DOESN'T works:

$result = mysql_query("SELECT * FROM auctions WHERE type='Cars' AND  name LIKE '%".$searchterm."%' OR Address LIKE '%".$searchterm."%' OR state like '%".$searchterm."%'");

 

What I'm trying to do is say "give me all the results from type:Cars.  Instead, it ignores the WHERE type='Cars' statement, and returns results for all types.  It frustrates me because I use the same exact query in a thousand other places, and it works everywhere else.  For example:

 

$sql = "SELECT * FROM auctions WHERE type='Boats' AND state='$v4' ORDER BY $v1 $v2";

 

works just fine.  I'm not exactly an expert on any of this, but I can see no logical reason why this works, but the Search code doesn't.  They appear in all ways identical, at least as far as query structure goes.  Can anyone spot where I screwed up?

Thanks!

Kyle

 

[Pikachu2000]

You'll need to force the precedence with parentheses.

[Mavent]

You'll need to force the precedence with parentheses.

 

Ah.  Is that because of the recursive searchterm instances?  I'm trying to pound the concepts into my head-  is it conceptually the same thing as writing "WHERE type='y' AND name LIKE 'z' OR type='y' AND name LIKE 'z'  OR type='y' AND name LIKE 'z'"?

In other words, the type has to be set for each Or statement?  I'm trying to wrap my head around the concepts so that one day I shall return, a .php Jedi Master! 

Thanks Pika!

[Andy-H]

$result = mysql_query("SELECT * FROM auctions WHERE type='Cars' AND (  name LIKE '%".$searchterm."%' OR Address LIKE '%".$searchterm."%' OR state like '%".$searchterm."%' )");

 

 

MySQL row:
type    - Cars
name    - testing
address - 123 fake street
state   - enabled


SEARCHTERM - 'fake street'


WHERE CLAUSE:
WHERE true AND false OR true OR false  // this will result true if any argument equates to true
WHERE true AND ( false OR true OR false ) // this will return true if argument 1 AND ( 2 OR 3 OR 4 ) are true

[Pikachu2000]

The way it is now, it will return results that match WHERE type='Cars' AND  name LIKE '%$searchterm%, but it will also return results that match OR Address LIKE '%".$searchterm."%' OR state like '%$searchterm%'" regardless of whether they match the first two parameters.

[Mavent]

I rewrote it like so:

$result = mysql_query("
	SELECT * FROM auctions 
	WHERE type='Cars' 
	AND (name LIKE '%".$searchterm."%' 
			OR Address LIKE '%".$searchterm."%' 
			OR city LIKE '%".$searchterm."%' 
			OR phone LIKE '%".$searchterm."%' 
			OR zip LIKE '%".$searchterm."%' 
			OR website LIKE '%".$searchterm."%' 
			OR state like '%".$searchterm."%')
	");

 

But of course, this being me, it's still doing the exact same thing as before: returning ALL types.  Clearly there's something I'm not grasping: doesn't the "WHERE type='Cars', outside of the Parens, force the query to assume Type as a per-requisit to the results within the parens?

Thanks!

[Andy-H]

Should work :s

[Mavent]

Should work :s

 

It's not the first time that my code has defied the laws of the universe. 

 

EDIT: As it turns out, it was that laziest of errors on my part, "not refreshing the cache".  I shall now hang my head in shame and contemplate seppeku.