Why is this PDO query not working properly?
$colors = $_GET['color'];
$colors = explode(' ', $colors);
$colors = implode(',',$colors);
$items = $con -> prepare("SELECT * FROM item_descr WHERE color_base1 IN (".$colors.")");
$items ->execute();
while($info = $items->fetch(PDO::FETCH_ASSOC))
{
echo $info['color_base1'];
}
You need to escape the $colors or you are subject to a SQL injection attack. There is a little-known PHP function array_fill that is GREAT for this:
$colors = explode(' ', $_GET['color']));
$parameters = join(', ', array_fill(0, count($colors), '?');
$items = $con->prepare("SELECT * FROM item_descr WHERE color_base1 IN ({$parameters})");
$items ->execute($colors);
while($info = $items->fetch(PDO::FETCH_ASSOC)) {
echo $info['color_base1'];
}
It appears your problem is that your colors weren't wrapped with quotes, but that problem goes away in my code because it uses bound parameters.
You need to escape $colors items with quotes to fit IN() statement if are dealing with strings:
SELECT * FROM item_descr WHERE color_base1 IN ('blue', 'yellow', '#FF0000')
Probably your $color variable is just comma separated - add quotes.
By the way, to inspect your MySQL error use PDO::errorInfo after running the query:
var_dump($con -> errorInfo());
it will show something like:
You have an error in your SQL syntax; [...]
so you can fix your query.
If $colors can contain strings, then you should use
function quote_value(& $value, $key) { $value = "'$value'"; }
$colors = $_GET['color'];
$colors = explode(' ', $colors);
array_walk($colors, 'quote_value');
$colors = implode(',',$colors);
// the rest of your code
