current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 11 down vote favorite
4

I'm using PDO to execute a statement with an IN clause that uses an array for it's values:

$in_array = array(1, 2, 3);
$in_values = implode(',', $in_array);
$my_result = $wbdb->prepare("SELECT * FROM my_table WHERE my_value IN (".$in_values.")");
$my_result->execute();
$my_results = $my_result->fetchAll();


The above code works perfectly fine, but my question is why this doesn't:

 $in_array = array(1, 2, 3);
    $in_values = implode(',', $in_array);
    $my_result = $wbdb->prepare("SELECT * FROM my_table WHERE my_value IN (:in_values)");
    $my_result->execute(array(':in_values' => $in_values));
    $my_results = $my_result->fetchAll();

This code will return the item who's my_value equals the first item in the $in_array (1), but not the remaining items in the array (2, and 3).

share|improve this question
    
Refer this. See if it can help. – Bhavik Shah Feb 8 '13 at 7:31
up vote 21 down vote accepted

PDO is not good with such things. You need to create a string with question marks dynamically and insert into query.

$in  = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE my_value IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($in_array);
$data = $stm->fetchAll();
share|improve this answer
    
Thanks, though I see how that could cause some confusion if I were to need those placeholders elsewhere in my statement. Do you recommend any libraries? Maybe the one with the getAll function? – iRector Feb 8 '13 at 7:59
1  
Sure. I had no answer to that question for a while - so, I had to write it myself. You can find the link in my profile. Feel free to ask any questions regarding usage or whatever issues. – Your Common Sense Feb 8 '13 at 8:09
    
Your answer helped me for the time being, thank you. I can't find any links in you profile. If your solution is not available any more, are there any intelligent libraries these days you could recommend? – san.chez Jul 30 '15 at 1:34
    
Can you explain what the -1 is for after the count()? – Robert Rocha Jun 8 '16 at 21:30
    
@RobertRocha because of the last '?' in that line, since the last one must not have a comma behind it. That one is always added so we need one less from the array. – msoft Jun 20 '16 at 8:22
up vote 6 down vote

Variable substitution in PDO prepared statements doesn't support arrays. It's one for one.

You can get around that problem by generating the number of placeholders you need based on the length of the array. 

$variables = array ('1', '2', '3');
$placeholders = str_repeat ('?, ',  count ($variables) - 1) . '?';

$query = $pdo -> prepare ("SELECT * FROM table WHERE column IN($placeholders)");
if ($query -> execute ($variables)) {
    // ...
}
share|improve this answer
    
Why count ($variables) - 1) . '?'; Why not just count($variable) – Robert Rocha Jun 8 '16 at 21:43
    
@RobertRocha Because you need one fewer commas than there are variables – GordonM Jun 9 '16 at 13:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.