WHERE OR stops LIKE from working

Question

I have this query being generated by codeignter:

SELECT `games_link`.`APPID`, `games_other`.`name`, `games_other`.`logo`, `platforms`.`PID`, `platforms`.`name` AS pname, `platforms`.`logo` AS plogo 
FROM (`games_link`) 
LEFT JOIN `games_platforms` ON `games_platforms`.`APPID` = `games_link`.`APPID`
LEFT JOIN `platforms` ON `platforms`.`PID` = `games_platforms`.`PID` 
LEFT JOIN `games_other` ON `games_other`.`APPID` = `games_link`.`GB_ID` 
WHERE `games_platforms`.`PID` = '94' 
OR `games_platforms`.`PID` = '139' 
OR `games_platforms`.`PID` = '35' 
OR `games_platforms`.`PID` = '129' 
OR `games_platforms`.`PID` = '146' 
OR `games_platforms`.`PID` = '20' 
OR `games_platforms`.`PID` = '145' 
AND `games_other`.`name` LIKE '%knack%' LIMIT 15

enter image description here

When this runs it does not filter via the LIKE, it just returns everything, But if I run this:

SELECT `games_link`.`APPID`, `games_other`.`name`, `games_other`.`logo`, `platforms`.`PID`, `platforms`.`name` AS pname, `platforms`.`logo` AS plogo 
FROM (`games_link`) 
LEFT JOIN `games_platforms` ON `games_platforms`.`APPID` = `games_link`.`APPID` 
LEFT JOIN `platforms` ON `platforms`.`PID` = `games_platforms`.`PID` 
LEFT JOIN `games_other` ON `games_other`.`APPID` = `games_link`.`GB_ID` 
WHERE `games_other`.`name` LIKE '%knack%' LIMIT 15

It runs the LIKE. enter image description here

You might want to consider reading these [two](http://stackoverflow.com/questions/6552821/codeigniter-parentheses-in-dynamic-active-record-query) [questions](http://stackoverflow.com/questions/6470267/grouping-where-clauses-in-codeigniter). — Maxime Morin, Aug 05 '14 at 11:50
Use braces in your query `( cond1 or cond2 or cond3) and cond4`. Is it possible in your system? — ngrashia, Aug 05 '14 at 11:50

score 4 · Accepted Answer · answered Aug 05 '14 at 11:39

4

Presumably you intend for any of the game types to be combined with the and. However, your logic is incorrect because you have no parentheses. The easiest way to fix it is to use in:

WHERE `games_platforms`.`PID` in (94, 139, 35, 129, 146, 146, 20, 145) and
      `games_other`.`name` LIKE '%knack%' ;

In the future, use parentheses around conditions in the where clause until you fully understand the precedence rules for or and and.

answered Aug 05 '14 at 11:39

Gordon Linoff

1,242,037
58
646
786

This is being generated by codeignters active record. So cant really avoid stuff like this easily. – Tristan Cunningham Aug 05 '14 at 11:44
Have you seen the comment of Maxime Morin to your original post? I think this would help you. – schlonzo Aug 05 '14 at 11:52
I already fixed it before that with: ->where('games_platforms.PID IN (94,'.implode(",", $plat).')') – Tristan Cunningham Aug 05 '14 at 11:54

score 0 · Answer 2 · answered Aug 05 '14 at 11:52

Since you say it is not possible to generate query in in clause as suggested by Gordon Linoff, Try if this is possible:

WHERE 
( -----> Open paranthesis here
`games_platforms`.`PID` = '94' 
OR `games_platforms`.`PID` = '139' 
OR `games_platforms`.`PID` = '35' 
OR `games_platforms`.`PID` = '129' 
OR `games_platforms`.`PID` = '146' 
OR `games_platforms`.`PID` = '20' 
OR `games_platforms`.`PID` = '145' 
) -----> Close paranthesis here
AND `games_other`.`name` LIKE '%knack%' LIMIT 15

WHERE OR stops LIKE from working

2 Answers2