phpmysqlsqlcodeigniter

WHERE OR stops LIKE from working

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

Solution

  • 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.