php pdo binding multiple values issue

Question

I have the following code in php to query the database based on an array sent by the client.

$limit = $_POST['limit'];
$userArray = json_decode($_POST['arr'], true);
$queryPlaceholders= implode(',', array_fill(0,count($userArray), '?'));
$stmt = $db->prepare("SELECT * FROM tableA
                          WHERE tableA.id IN (".$queryPlaceholders.")
                          LIMIT ?");
foreach($userArray as $k => $val){
    $stmt->bindParam(($k+1), $val);
}
$stmt->bindValue(count($userArray) + 1, (int)trim($limit), PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo $result;

This code appears to have an error. If I send an array containing the values 11 & 17, the query seems to only run with the value 17, not both 11 and 17.

If I print_r($userArray) I get Array ( [0] => 11 [1] => 17 )

so I know php has the correct array. However, running this query with the code above, and running the query below yields different answers:

SELECT * FROM tableA
WHERE tableA.id IN (11,17)
LIMIT 10

When running the code above, It appears to infact run this query?

SELECT * FROM tableA
WHERE tableA.id IN (17)
LIMIT 10

I've also placed statements in the foreach loop that tells me both elements of the array (11 & 17) are being bound to the $stmt

eggyal · Accepted Answer · 2012-10-12 11:16:04Z

3

The problem is that you are using bindParam():

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was use to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

Since $val is changed on each iteration of the foreach loop, it ultimately is the same for each placeholder when the query is finally executed.

answered Oct 12, 2012 at 11:16

eggyal

127k18 gold badges221 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php pdo binding multiple values issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related