First, let's check out below codes:
<?php $dbh = new PDO('mysql:host=localhost;dbname=test', "test"); $query = <<prepare($query); $bind_params = array(':username' => "laruence", ':password' => "weibo"); foreach( $bind_params as $key => $value ){ $statement->bindParam($key, $value); } $statement->execute();
What is the SQL executed finally? Is there any problem with above codes?
Many people may think the query executed is :
INSERT INTO `user` (`username`, `password`) VALUES ("laruence", "weibo");
But the query actually gets executed is :
INSERT INTO `user` (`username`, `password`) VALUES ("weibo", "weibo");
Is it a big trap? This issue is drafted in a bug report : #63281.
The reason comes from the difference between bindParam and bindValue, the second parameter in bindParam should be a reference.
Let's take a look at the foreach statement above:
<?php foreach( $bind_params as $key => $value ){ $statement->bindParam($key, $value); }
It's the same as:
<?php //The first iteration $value = $bind_params[":username"]; $statement->bindParam(":username", &$value); //Now username is the reference to the $value //The second iteration $value = $bind_params[":password"]; //oops! $value is overrided with value of password $statement->bindParam(":password", &$value);
So we should pay attention to this trap when using bindParam together with foreach. What's the right way to achieve what we expected?
1. Don't use foreach, manually assign values instead
<?php $statement->bindParam(":username", $bind_params[":username"]); $statement->bindParam(":password", $bind_params[":password"]);
2. Replace bindParam with bindValue, or directly pass the parameters in the execute method
3. Use foreach and reference(Don't recommend this method)
<?php foreach( $bind_params as $key => &$value ) { //Note here $statement->bindParam($key, $value); }
At last, you should pay attention to the use of foreach when need to pass reference to a function call.
Hey! Thanks a lot man!!
This saved my life.
I had the same problem. After use the reference in my foreach, worked like a charm.