我有一个函数来绑定/准备语句然后执行它:
function db_update ($table, $set, $where_col, $where_val)
{
$s = "UPDATE `$table` SET ";
foreach ($set as $k => $v)
$s.= "$k = :".trim($k).", ";
$s = trim($s, ', ');
$s.= " WHERE `$where_col` = :$where_col";
$binds = array();
foreach ($set as $k => $v)
$binds[':'.$k] = trim($v);
$binds[':'.$where_col] = trim($where_val);
return db_run($s, $binds);
}
基本上db_run
你常用的PDO方法:
function db_run($stmt, $binds = array())
{
// ...
$sth = $db->prepare($stmt);
$sth->execute($binds);
// ...
}
示例用法 A:
db_update('table', ['color' => 'red'], 'fruit', 'apple');
结果:
- 准备:
UPDATE table SET color = :color WHERE fruit = :fruit
- 实际:
UPDATE table SET color = 'red' WHERE fruit = 'apple'
这运行良好,但我的主要问题是如果用法是这样的:
示例用法 B:
db_update('table', ['color' => 'red'], 'color', 'black');
结果:
- 准备:
UPDATE table SET color = :color WHERE color = :color
- 实际:
UPDATE table SET color = 'black' WHERE color = 'black'
我怎样才能使实际结果是:
UPDATE table SET color = 'red' WHERE color = 'black'
您得到该结果是因为您在查询的两个位置(SET 和 WHERE(都使用了:color
参数。 因此,db_update()
函数需要为 WHERE 子句使用不同的参数名称。
function db_update ($table, $set, $where_col, $where_val)
{
$s = "UPDATE `$table` SET ";
foreach ($set as $k => $v)
$s.= "$k = :".trim($k).", ";
$s = trim($s, ', ');
$s.= " WHERE `$where_col` = :where_$where_col";
$binds = array();
foreach ($set as $k => $v)
$binds[':'.$k] = trim($v);
$binds[':where_'.$where_col] = trim($where_val);
return db_run($s, $binds);
}
这应该导致准备的结果UPDATE table SET color = :color WHERE color = :where_color