PDO IN() Array Statement AND a placeholder
Solution 1
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
Solution 2
$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);
Solution 3
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)
Related videos on Youtube
Maverick
Updated on June 30, 2022Comments
-
Maverick almost 2 years
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..
-
Ravi Dhoriya ツ about 10 yearsThis
rtrim(str_repeat('?, ', count($values)), ', ') ;
is nice. thank you :)
-
-
Maverick over 12 yearsThanks 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 over 12 yearsThis 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?
-
Tadeck over 12 years@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))
. -
Maverick over 12 years@Tadeck: Both Tadeck's & Charles answers are excellent. I wish I can accept both on SO.
-
Tadeck over 12 years@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. Butarray_slice()
is something one can take into consideration.