1

Lets pretend I've got some SQL and variables such as:

$number = 5;

And my PDO sql is:

SELECT * FROM things where ID = :number

Except, number is actually an array such as:

$number = array(1,2,3);

Which doesn't work out at all for 

SELECT * FROM things where ID in ( :number )

How can I accomplish this with PDO? Presently I'm looping through the array, type-casting to int, and injecting the string to the SQL before I do any PDO binding on it.

user3738703
  • 17
  • 3
Incognito
  • 20,537
  • 15
  • 80
  • 120
  • possible duplicate of [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) –  Jun 03 '11 at 20:17
  • I'm personally using a PDO wrapper which provides for `WHERE ID IN (??)` alternative syntax to pass arrays. But as workaround with standard functions you could often utilize the SQL function `FIND_IN_SET(id,:number)` and just pass the array as list in a string. – mario Jun 03 '11 at 23:45

4 Answers4

2

The most common solution is to implode number (delimiting by a comma) and put the resulting string in to where in() without binding it as a param. Just be careful, you have to make sure it is safe for query, in this case.

Same thing here: Can I bind an array to an IN() condition?

Community
  • 1
  • 1
SamT
  • 10,374
  • 2
  • 31
  • 39
0

If you want to at least get rid of the loop, you can generate your string as such:

$numberArray = array(1,2,3);
$number = implode(",", $numberArray);

// $number is now the string "1,2,3"
Trott
  • 66,479
  • 23
  • 173
  • 212
  • Err, no. The loop is to sanitize my user input before SQL injection. I'm not about to implode an array without making sure someone didn't put bobby tables in it. – Incognito Jun 03 '11 at 20:19
  • @Incognito: Doesn't PDO do that for you? – gen_Eric Jun 03 '11 at 20:23
  • @Incognito: Sanitizing is not mentioned in the question. Someone reading the question as it is currently written might reasonably conclude, as I did, that either any necessary processing/validation had already happened during the generation of the array or else that sanitization is not necessary because the array is assigned as a series of integer values as shown in the question. – Trott Jun 03 '11 at 20:56
0

You can also try

$foo = conection->prepare("SELECT * FROM table WHERE id IN(:number)");

foreach ($number as $val) {
$foo->bindValue(':number', $val);
$foo->execute();
}

Hope it helps!

Maverick
  • 1,988
  • 5
  • 24
  • 31
  • 3
    I don't think running the query multiple times is the solution here. The whole point of `IN` is to run 1 query for multiple items. – gen_Eric Jun 03 '11 at 21:31
-1

You can't bind that variable directly into statement.

Do something like this:

<?php
    $number = array(1, 2, 3);
    $number = implode(', ', $number);

    // Bind it here...

    $sql = "SELECT * FROM table WHERE id IN(:number);";
Otar
  • 2,561
  • 1
  • 20
  • 24