How to use the OR statement in MySQL while using the LIKE search statement

Question

Hi,

I am trying to create a good little search statement that searches muitple fields using the with different search terms depending on what is returned. I am basiclly setting an order in which I want to search if one search term is not found try the next one. However I am using a WHERE clause to seperate search only one type of data. Currently I am getting some strange results returned because I don't think my order is correct, it seems that my WHERE clause is being ignored.

here is my statement (I am usig PHP):

mysql_query("SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id WHERE i.item_status = 'active' AND (i.item_name LIKE '%".$_SESSION['item']."%') OR i.item_description LIKE '%".$_SESSION['description']."%' OR i.item_name LIKE '%".$_SESSION['description']."%' "); 

Thank you in advance.

Answer 1

Hi,

The OR operator has lower precedence than the AND operator. http://dev.mysql.com/doc/refman/4.1/en/operator-precedence.html

Put all the ORs inside parentheses (one that contains all of them, not one for each of them ;) ).

Answer 2

Move the right parenthesis to the end of the statement:

SELECT * 
FROM item AS i LEFT JOIN country AS c 
ON i.country_id = c.country_id 
WHERE i.item_status = 'active' 
AND (i.item_name LIKE '%".$_SESSION['item']."%' 
OR i.item_description LIKE '%".$_SESSION['description']."%' 
OR i.item_name LIKE '%".$_SESSION['description']."%')

Answer 3

It is your parentheses in the wrong place. Here is the correct code:

mysql_query("SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id WHERE i.item_status = 'active' AND (i.item_name LIKE '%".$_SESSION['item']."%' OR i.item_description LIKE '%".$_SESSION['description']."%' OR i.item_name LIKE '%".$_SESSION['description']."%')");

Answer 4

Why don't you use full-text search feature?

Need to alter table structure for create fulltext search index.

ALTER TABLE item ADD FULLTEXT (item_name, item_description);

Then your queries turns to:

mysql_query("
SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id 
WHERE i.item_status = 'active' AND 
MATCH (i.item_name, i.item_description)
AGAINST (" . $_SESSION['item'] . " " . $_SESSION['description'] . ");
");

Simple, accurate, and faster.