PDO query returning more values than intended using LIKE CONCAT

Question

I have a code that is supposed to SELECT items from a table where the fields color_base1 and color_base2 are similar to specific colors.

However, currently my code is just returning all the fields in my table, kind of ignoring the LIKE I am applying:

$result3 = $con -> prepare("SELECT  * FROM item_descr WHERE (color_base1 LIKE CONCAT ('%', ? , '%') OR color_base2 LIKE CONCAT ('%', ? , '%')) AND id_item != $itemId LIMIT 4");
$result3 -> execute(array("$color_base1", "$color_base2"));
$row3 = $result3->fetch();

Would anyone have any input on how the SQL is built and how I should build it?

You are selecting all records where color_base1 CONTAINS the string $color_base1 PLUS (union, or...) all records where color_base2 CONTAINS the stirng $color_base2. Let's say the colors stored in color_base1/2 are RGB hex color codes (DC0000 for deep red), if you search WHERE color_base1 LIKE "%C0%" it will match the record with DC0000, but if you search LIKE "%D0C%" it won't match. Is that your issue? Otherwise I didn't get your problem... — tobia.zanarella, Jun 18 '13 at 15:07
I am trying to obtain the records in which color_base1 is similar to $color_base1 OR color_base2 is similar to $color_base2. I use the % since my colors are in their actual name (green, red..) and sometimes I have "light green", so "light green" should be similar to "green" — samyb8, Jun 18 '13 at 15:31
Ok but what's wrong with your query? Apart from some PHP errors, your query should do the work. Unless you want something a bit different than OR: do you need an XOR (see: http://en.wikipedia.org/wiki/Exclusive_or)? — tobia.zanarella, Jun 18 '13 at 16:27

Your Common Sense · Accepted Answer · 2013-06-18T16:54:19.540

You need to double-check if both your variables contain any value. Otherwise an empty one will make query return all the rows.

Though it would be better to normalize your table structure and data and get rid of LIKE at all.

Also note that you have to bind item id as well
And variables in PHP have to be addressed without quotes. Also consistent naming is a great thing. There is nothing distinctive in your result. it's just a PDO statement, without any special meaning for your application.

$sql = "SELECT  * FROM item_descr WHERE 
(color_base1 LIKE CONCAT ('%', ? , '%') OR color_base2 LIKE CONCAT ('%', ? , '%')) 
AND id_item != ? LIMIT 4"
$stmt = $con->prepare($sql);
$stmt->execute(array($color_base1, $color_base2, $itemId));
$row = $stmt->fetch();

PDO query returning more values than intended using LIKE CONCAT

1 Answers1