Creating shopping basket using PHP

Question

I am trying to create a shopping basket with PHP, I want to pass through a bunch of IDS and query the database for them. At the moment I recieve this error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(1\', \'10\', \'6\', \'23)'' at line 1

Here is the function I am using

function find_item_db($product_code) {
try{
$query = substr($product_code, 0,-1);
$product_codes = explode(",", $query);
$ids = '';
foreach($product_codes as $code) {
$params[] = $code;
$ids .= '?, ';
} 
$ids = '(' . rtrim($ids, ', ') . ')';
//we need to get product name and price from database.
$sql = "SELECT * FROM `Sweets` WHERE `Sweet_ID` IN $ids";
$statement = $this->connection->prepare($sql);
$statement->execute($params);
return $result = $statement->fetchAll();
}
catch(PDOException $e)
{
    echo "Error: " . $e->getMessage(). "<br/>";
}    

}

Can anyone see where I am getting a syntax error here?

Answer 1

You need to have each value have its own placeholder. Try:

$product_codes = explode(",", $query);
$ids = '';
foreach($product_codes as $code) {
    $params[] = $code;
    $ids .= '?, ';
} 
$ids = '(' . rtrim($ids, ', ') . ')';
//we need to get product name and price from database.
$sql = "SELECT * FROM `Sweets` WHERE `Sweet_ID` IN $ids";
$statement = $this->connection->prepare($sql);
$statement->execute($params);

Note $ids is just a list of placeholders, the actual values are bound in the execute.

Rough demo of how it works: https://eval.in/520334