How can I use a php array in my sql query?

Question

I'm trying to use an array in my sql query.

Could someone help me please ?

$categories = [256, 249, 681, 682, 683, 684];

$sql = "SELECT * FROM `database`.`category` WHERE `id_category` = (:categories);";    
$request= $database->prepare($sql);    
$request->bindValue(':categories', $categories, PDO::PARAM_INT);    
$request->execute();    
$result = $request->fetchAll(PDO::FETCH_ASSOC);

The value returned from my array is 1, how can I explode my array in my SQL request ? Thanks guys.

score 2 · Answer 1 · answered Nov 18 '22 at 08:45

I would use the IN keyword which takes a comma separated list. To make it prepared replace any value with a ? placeholder, so the query looks like

SELECT * FROM `database`.`category` WHERE `id_category` IN (?,?,?,?,?,?);

Instead of binding each value to a variable, you can pass the values directly to execute().

$categories = [256, 249, 681, 682, 683, 684];
$catQuery = implode(',', array_map(fn($x) => '?', $categories));

$sql = "SELECT * FROM `database`.`category` WHERE `id_category` IN ($catQuery);";
$request= $database->prepare($sql);
$request->execute($categories);
$result = $request->fetchAll(PDO::FETCH_ASSOC);

How can I use a php array in my sql query?

1 Answers1