[坑]foreach中使用bindParam

问题发现

在学习开发模式,写数据库访问对象模式的时候遇到了一个奇怪的问题

$sql='UPDATE userTable SET id=:id ,name=:name WHERE id=:key';
$keyedArray=['id' => 2, 'name' => 'ccc'];
$stmt = $this->dbh->prepare($sql);
foreach ($keyedArray as $column => $value) {
    $column = ':' . $column;var_dump($value);
    $stmt->bindParam($column, $value);
}
$stmt->bindParam(':key', $keyedArray[$this->primaryKey]);
$stmt->execute();

执行后报错

Array
(
    [0] => HY000
    [1] => 1366
    [2] => Incorrect integer value: 'ccc' for column 'id' at row 1
)

改写成下面这样这样却发现正常执行

$sql='UPDATE userTable SET id=:id ,name=:name WHERE id=:key';
$keyedArray=['id' => 2, 'name' => 'ccc'];
$stmt = $this->dbh->prepare($sql);
$columns=[];
$values=[];
foreach ($keyedArray as $column => $value) {
    //$column = ':' . $column;var_dump($value);
    $columns[]=$column;
    $values[]=$value;
}
$stmt->bindParam($columns[0],$values[0]);
$stmt->bindParam($columns[1],$values[1]);
$stmt->bindParam(':key', '$keyedArray[$this->primaryKey]');
$stmt->execute();

然互我就……WTF
在这样试了之后我发现,啥?为什么$value在执行了bindParam后类型变了?引用传值?!我对bindParam的认识竟然这么模糊?!

foreach ($keyedArray as $column => $value) {
    $column = ':' . $column;
    var_dump($value);
    $stmt->bindParam($column, $value);
    var_dump($value);
}
/*
/home/netair/PhpstormProjects/PHPDesignPatterns/DAO.php:60:
int(2)
/home/netair/PhpstormProjects/PHPDesignPatterns/DAO.php:62:
string(1) "2"
/home/netair/PhpstormProjects/PHPDesignPatterns/DAO.php:60:
string(3) "ccc"
/home/netair/PhpstormProjects/PHPDesignPatterns/DAO.php:62:
string(3) "ccc"
*/

真相大白

赶紧去看了文档发现了这句话

不同于 PDOStatement::bindValue() ,此变量作为引用被绑定,并只在 PDOStatement::execute() 被调用的时候才取其值。 不同于 PDOStatement::bindValue() ,此变量作为引用被绑定,并只在 PDOStatement::execute() 被调用的时候才取其值

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, …] )
这里的引用传值再加上只有execute时才取其值,自然拿到的值都是最后一个循环的value值’ccc’

解决办法

  1. 将bindParam换为bindValue
  2. 之后搜了一下发现中招的人应该不少…这里还有一种解决办法
$stmt = $db->prepare($sql);
$params = array(':City' => 'string1', ':State' => 'string2');
foreach ($params as $key => &$val) {
    $stmt->bindParam($key, $val);
}
$stmt->execute();