PDO bindParam not working in foreach

Question

I am using PDO for an application but getting a problem for PDO bindParam(). I have an array and I want to use the values of array for PDO bindParam() using for loop or foreach() but an unexpected result is getting by foreach(). When I used bindParam() in for loop, it worked fine. What I tried was

$con = $this->connection();
$stmt = $con->prepare($sql);

for($i = 0; $i < count($params); $i++){
   $stmt->bindParam($i + 1, $params[$i], PDO::PARAM_STR, 10);
}
$stmt->execute();
$result = $stmt->fetchAll();//$result is OK

But when I used bindParam() in foreach() then I got an empty array() as result. Below the codes

$con = $this->connection();
$stmt = $con->prepare($sql);

foreach($params as $key=>$val){ //Here
    $stmt->bindParam($key + 1, $val, PDO::PARAM_STR, 10);
}
$stmt->execute();
$result = $stmt->fetchAll(); //$result is an empty array

I'm wondering why this happened. I can't find out the reason. Any information will be appreciated.

EDIT : I solved my problem using bindValue() instead.

@Jez It is from function parameter! – MH2K9 Oct 02 '14 at 15:22 — MH2K9, Oct 02 '14 at 15:22

score 14 · Accepted Answer · answered Oct 02 '14 at 15:22

14

use bindValue() instead of bindParam(). bindParam() binds to a reference, so when you execute the query all the parameters use the last value of $val.

answered Oct 02 '14 at 15:22

Barmar

741,623
53
500
612

I was just about to post this. This is the correct answer. – Tibor B. Oct 02 '14 at 15:23
`bindValue()` doesn't have the `driver_options` parameter. Is that necessary for your query? – Barmar Oct 02 '14 at 15:35
@Barmar how can I show my full SQL query string before executing? – Janie Jul 07 '17 at 12:05
@Janie See https://stackoverflow.com/questions/210564/getting-raw-sql-query-string-from-pdo-prepared-statements – Barmar Jul 07 '17 at 16:12

score 6 · Answer 2 · answered Oct 02 '14 at 15:22

If you already have the items in an array, there's no reason to call $stmt->bindParam in a loop; just do:

$con = $this->connection();
$stmt = $con->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetchAll();

Per the PHP documentation:

Execute the prepared statement. If the prepared statement included parameter markers, you must either:

call PDOStatement::bindParam() to bind PHP variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers

or pass an array of input-only parameter values

PDO bindParam not working in foreach

2 Answers2

Linked