PDO bindParam doesn't work in foreach
I am using PDO
for app but getting problem for PDO bindParam()
. I have an array and I want to use the values of the array for PDO bindParam()
using a loop for
or foreach()
, but I get an unexpected result foreach()
. When I used bindParam()
in a loop for
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()
result. Below 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 am wondering why this happened. I cannot find out the reason. Any information would be appreciated.
EDIT: I solved the problem by using bindValue()
instead.
use bindValue()
instead bindParam()
. bindParam()
binds to a reference, so all parameters use the last value when executing the request $val
.
If you already have elements in the array, there is no reason to call $stmt->bindParam
in a loop; just do:
$con = $this->connection();
$stmt = $con->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetchAll();
In PHP Documentation :
Execute the prepared statement. If the prepared expression includes parameter markers, you must:
call PDOStatement :: bindParam () to bind PHP variables to a parameter markers: bind variables pass their value as input and take an output value, if any, of their associated parameter markers
or pass an array of parameter values for input only