PDO：如果UPDATE：column和WHERE：column相同，则阻止值覆盖

Question

I've a function that binds/prepares the statement then execute it:

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);
}

Basically db_run does your usual PDO methods:

function db_run($stmt, $binds = array())
{
    // ...      
    $sth = $db->prepare($stmt);
    $sth->execute($binds);      
    // ...
}

Sample usage A:

db_update('table', ['color' => 'red'], 'fruit', 'apple');

Result:

• Prepared: UPDATE table SET color = :color WHERE fruit = :fruit
• Actual: UPDATE table SET color = 'red' WHERE fruit = 'apple'

This runs just fine, but my main issue is what if the usage is like this:

Sample usage B:

db_update('table', ['color' => 'red'], 'color', 'black');

Result:

• Prepared: UPDATE table SET color = :color WHERE color = :color
• Actual: UPDATE table SET color = 'black' WHERE color = 'black'

How can I make it so the actual result is:

UPDATE table SET color = 'red' WHERE color = 'black'

Answer 1

You're getting that result because you're using the :color parameter in both places (the SET and the WHERE) in your query. So your db_update() function needs to use a different parameter name for the WHERE clause.

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);
}

This should result in a prepared result of UPDATE table SET color = :color WHERE color = :where_color