CODEWITHSUNDEEP
phpmysqlsqlselect
Tom Groot
Tom Groot

Reputation: 1180

OR and AND combination fail MySQL

I tried to figure out which mysql code to use. I tried the following script which contains all search features I need:

SELECT * FROM `zk_posts` WHERE `form_id` IN (3,6) 
AND FROM_UNIXTIME(published, '%Y') = 2013 
AND `message` LIKE '%search%' 
OR `description` LIKE '%search%' 
OR `name` LIKE '%search%';

At first I want to only show posts where form_id is 3 or 6 and the published year is 2013. At the same time I want to search inside my text columns, if there is any match inside one of these three, but by using OR in combination with AND it doesn't work the way I want. If I run this mysql it returns posts which for example don't match with the form_id.

Can anyone tell me how he or she would solve this.

Upvotes: 0

Views: 95

Answers (3)

MrCleanX
MrCleanX

Reputation: 416

Try it like this...

SELECT * FROM `zk_posts` WHERE `form_id` IN (3,6) 
AND FROM_UNIXTIME(published, '%Y') = 2013 
AND (`message` LIKE '%search%' 
OR `description` LIKE '%search%' 
OR `name` LIKE '%search%');

Upvotes: 2

PM 77-1
PM 77-1

Reputation: 13334

Is this what you want? (please notice parentheses around OR conditions)

 SELECT * FROM `zk_posts` 
WHERE `form_id` IN (3,6) AND FROM_UNIXTIME(published, '%Y') = 2013 AND 
(`message` LIKE '%search%' OR `description` LIKE '%search%' OR `name` LIKE '%search%');

Upvotes: 0

Marc B
Marc B

Reputation: 360662

You need brackets to enforce the order in which mysql evaluates those conditions. Probably something like this:

SELECT ...
WHERE (`form_id` in (3,6))
    AND (FROM_UNIXTIME(published, '%Y') = 2013)
    AND (
       message LIKE '%search%'
       OR description LIKE '%search%'
       OR name LIKE '%searc%'
    )

as written, your query is treated like

... WHERE (everything else)
    OR (message LIKE ...)
    OR (description LIKE ...)
    OR (name LIKE ...)

which would evaluate to TRUE if the search terms are in any of those three fields, regardless of the rest of the conditions.

Upvotes: 0

Related Questions