PDO IN() Array Statement AND a placeholder

Question

I found this code on SO, which is great for using PDO and the IN() statement together.

$values = explode(',', $values) ; # 1,4,7

$placeholders = rtrim(str_repeat('?, ', count($values)), ', ') ;
$query = "SELECT * FROM table WHERE id IN ($placeholders)";

$stm = $db->prepare($query) ;
$stm->execute($values) ;

However, how can I mix in another addition to the query so the query looks like this:

$query = "SELECT * FROM table WHERE id IN ($placeholders) AND product=?";
$stm = $db->prepare($query) ;
$stm->execute(array($values,$product)) ; //error happens when adding product placeholder

I thought this would work but I get:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in line 3 (the $stm line)

Any idea how to get this to behave as intended?

UPDATED execute to array, still not working..

This `rtrim(str_repeat('?, ', count($values)), ', ') ;` is nice. thank you :) — Ravi Dhoriya ツ, Mar 22 '14 at 08:04

score 8 · Accepted Answer · answered Jan 24 '12 at 18:13

Solution

This should work, if $values is an array:

$query = "SELECT * FROM table WHERE id IN ($placeholders) AND product=?";
$stm->execute(array_merge($values, array($product)));

Explanation

execute() expects one parameter - in this case an array - to be provided. By adding array_merge($values, array($product)) you create one array with $product added at the end, so the query should work correctly.

See the demo here: http://ideone.com/RcClX

score 7 · Answer 2 · answered Jan 24 '12 at 18:13

7

$stm->execute($values,$product) ; //error happens when adding product placeholder

The problem here is that execute needs a single array. You can't pass multiple arrays, and worse, you can't nest arrays.

We already have a perfectly good $values array, so let's reuse it after you create the placeholder string.

$values = explode(',', $values) ; # 1,4,7

$placeholders = rtrim(str_repeat('?, ', count($values)), ', ') ;
$query = "SELECT * FROM table WHERE id IN ($placeholders) AND product=?";

// New!
$values[] = $product;

$stm = $db->prepare($query);
$stm->execute($values);

answered Jan 24 '12 at 18:13

Charles

50,943
13
104
142

This looks like the most efficient answer so far, same thing as Tadeck's answer but cleaner execute statement with a single array. It should always also know that the product=? placeholder will always be the extra value not in the count, am I right? One more thing if so, if we were to add an additional item at the end of the query, such as, AND type=?, do we just say $values[] = $type; after the $values[] = $product; line, or can we merge them in one line? Something like, $values[] = ($product,$type); maybe? – Maverick Jan 24 '12 at 18:31
2

@Maverick: I did that (merged arrays within `execute()` call) on purpose, because you can always use `$values` in your code later. And if you do it after you append `$product`, then you will just need to deal with one additional element you did not want there :) But the decision is yours - just remember not to make the code complicated :) If you want to do something like `$values[] = ($product,$type)` in one line, just do this: `$values = array_merge($values, array($product,$type))`. – Tadeck Jan 24 '12 at 18:38
@Tadeck: Both Tadeck's & Charles answers are excellent. I wish I can accept both on SO. – Maverick Jan 24 '12 at 18:48
@bart: Using `array_splice()` means that you will first add some elements only to remove them later, to return `$values` to its original value. Not very efficient and, more importantly, may decrease (and probably will decrease) maintainability. But `array_slice()` is something one can take into consideration. – Tadeck Jan 24 '12 at 19:18

score 1 · Answer 3 · answered Jun 03 '15 at 08:37

And an other solution can be (if you like the :param_name = $value way, as me):

$params = array(
     ':product' =>  $product
);
$_in_params = array();
foreach ( $_in_values as $idx_in => $value_in)
{
    $_in_params[] = ':param_in_'.$idx_in;
    $params[':param_in_'.$idx_in] = $value_in;
}

$query .= "SELECT * FROM table WHERE id IN (".join(',',$_in_params).") AND product=:product";

I'm not sure if this is the best and the most optimal solution, but it's a little bit more human readable :) And it can be helpful if you have a big an complicated query and you want to debug it

(I'm curious if someone have a good argument why NOT to do in this way)

score 0 · Answer 4 · answered Jan 04 '17 at 16:15

Placeholders version if you need it

$values = [1, 4, 7, 8];
$placeholders =  preg_filter('/^/', ':prefix_', array_keys($values)));
$query = 'SELECT * FROM table WHERE id IN ( '. implode(', ', $placeholders) . ')';

$stmt = $db->prepare($query);

if (count($values) > 0) {
    foreach ($values as $key => $current_value) {
        $stmt->bindValue($placeholders[$key] , $current_value, PDO::PARAM_STR);
    }
}

$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Naftali · Answer 5 · 2012-01-24T18:09:01.470

0

You forgot to prepare it ^_^

$query = "SELECT * FROM table WHERE id IN ($placeholders) AND product=?";
$stm = $db->prepare($query) ;
$stm->execute($values,$product) ; //p00f

And aside from that execute() should only have one parameter

So the above won't work AT ALL!

See the DOCs

edited Jan 24 '12 at 18:09

answered Jan 24 '12 at 18:01

Naftali

144,921
39
244
303

Thanks Neal, however, I did prepare it, I was just showing the parts that changed between the two calls. I also changed it to use: $stm->execute(array($values,$product)); and I still get an error, I'll reedit and post above – Maverick Jan 24 '12 at 18:13

PDO IN() Array Statement AND a placeholder

5 Answers5

Solution

Explanation

Linked

Related