CODEWITHSUNDEEP
phpmysqlsql-like
user3369825
user3369825

Reputation: 99

MySQL LIKE statement returning odd results

When I do a LIKE MySQL query with multiple columns using AND and OR my filter results in the query such as by Price 0 AND 50000 stop working. If I take off the OR from my LIKE statement the search works fine. How do I properly search multiple columns using a LIKE statement?

Here is an example of none working statement where Price is ignored

SELECT * 
  FROM table 
 WHERE Price BETWEEN 0 AND 500000 
   AND class_id = 1 
   AND TotalSqFt BETWEEN 0 AND 999999999 
   AND Acres BETWEEN 0 AND 999999999 
   AND InclusionsFeatures LIKE '%horse%' 
    OR Remarks LIKE '%horse%' 
ORDER 
    BY Price DESC

This statement works correctly but now I can only do a LIKE query on one column.

SELECT * FROM table WHERE Price BETWEEN 0 AND 500000 AND class_id = 1 AND TotalSqFt BETWEEN 0 AND 999999999 AND Acres BETWEEN 0 AND 999999999 AND InclusionsFeatures LIKE '%horse%' ORDER BY Price DESC

Upvotes: 0

Views: 146

Answers (2)

Hamed Ghasempour
Hamed Ghasempour

Reputation: 445

In the first statement, when you add OR right after last AND, Mysql goes through until it runs OR. if OR condition passed, All before AND will ignore. So the query doesn't run correctly.

Look at this query:

SELECT
    * 
FROM
TABLE 
WHERE
    Price BETWEEN 0 
    AND 500000 
    AND class_id = 1 
    AND TotalSqFt BETWEEN 0 
    AND 999999999 
    AND Acres BETWEEN 0 
    AND 999999999 
    AND (InclusionsFeatures LIKE '%horse%' OR Remarks LIKE '%horse%' ) 
ORDER BY
    Price DESC

by grouping your LIKES by parentheses, it checks inside parentheses conditions with all other conditions with an AND. So query return data that match conditions inside parentheses and all other conditions.

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

You have mixed of AND and OR operators. So you can enclose the conditions correctly will work. For your OR condition you can enclose as follows:

SELECT * 
FROM table 
WHERE (Price BETWEEN 0 AND 500000) AND 
      (class_id = 1) AND 
      (TotalSqFt BETWEEN 0 AND 999999999) AND 
      (Acres BETWEEN 0 AND 999999999) AND 
      (
       (InclusionsFeatures LIKE '%horse%') OR (Remarks LIKE '%horse%')
      )
ORDER BY Price DESC

Upvotes: 0

Related Questions