Nested Where, Like Or like MySql Query doesn't care about Where Clause

Question

I'm working on a new project, and for this project I need a "search Engine" of shops stored in my database. I make a Like query on the name and the address of the shop, but only where the shop is valid (set to 1). My query looks like that :

SELECT * 
FROM (`shops`) 
WHERE `shop_valid` = 1 
  AND `shop_address` LIKE '%lyon%' 
   OR `shop_name` LIKE '%lyon%' 
ORDER BY `shop_offer` desc 
LIMIT 5

The results returned by this query are correct with the name and the address, but some of them have the "shop_valid" is set 0. And when I remove for example the clause on the shop address, the query works well with all "shop_valid" set to 1. Like this :

SELECT * 
FROM (`shops`) 
WHERE `shop_valid` = 1 
  AND `shop_name` 
LIKE '%lyon%' 
ORDER BY `shop_offer` desc 
LIMIT 10

Hope someone can help me, I'm being stuck for a while…

Thank you!

score 2 · Accepted Answer · answered Mar 14 '13 at 15:12

2

SELECT * FROM (`shops`) 
WHERE `shop_valid` = 1 
AND (`shop_address` LIKE '%lyon%' OR `shop_name` LIKE '%lyon%') 
ORDER BY `shop_offer` desc LIMIT 5

You have to use parantheses. When one part of an OR condition is true, the other one is irrelevant.

answered Mar 14 '13 at 15:12

fancyPants

50,732
33
89
96

Ok thank you! I'm using Code Igniter in this project so I'll try to accomplish the same query using the built-in Helpers... – BastienPenalba Mar 14 '13 at 19:12

Nested Where, Like Or like MySql Query doesn't care about Where Clause

1 Answers1