MySQL PDO how to bind params for IN()

Question

I'm using PHP, MySQL and PDO.

$ids = '1,2,3';

I have a query like this which works:

SELECT *
FROM table
WHERE id IN($ids)

This returns 3 rows as expected

If I use:

SELECT *
FROM table 
WHERE id IN(:ids)

$stmt = $this->db->prepare($q);
$params = array('ids' => $ids);
$stmt->execute($params);

It only returns 1 row.

How can I bind the ids?

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";

but how can I put my ids of 1,2,3 which are stored in $ids into $arr = [1,2,3] because if I write

 $arr = [$ids]

it's basically writing

 $arr = ['1,2,3']

instead of

$arr = [1,2,3]

Have a look at [this site](https://phpdelusions.net/pdo), search the page for `IN()` for an example. — Paul T., Dec 25 '17 at 02:20
Thanks Paul, I see the solution there but I'm faced with a new problem: See my edited question. — Gdev, Dec 25 '17 at 03:02

Gdev · Answer 1 · 2017-12-25T04:48:38.597

I figured it out:

$ids = '1,2,3';

Explode the $ids string into an array:

$ids_array = explode(',', $ids);

This gives:

$ids_array[] = 1;
$ids_array[] = 2;
$ids_array[] = 3;

Create a comma-delimited string of question marks. The number of question marks matches the number of array values

$in  = str_repeat('?,', count($ids_array) - 1) . '?';

This produces a string that looks like:

?,?,?

Put that string into the sql

$q = "SELECT *
    FROM table
    WHERE id IN($in) ";


$stmt = $this->db->prepare($q);

Execute the query, passing the array as a parameter

$stmt->execute($ids_array);

1 Answers1