How to use bind variables with Zend_Db_Table->update() in the where clause

Question

If I want to use the Zend_Db_Table->update() method to update my table with data, I cannot find anyway to use bind variables in the "where" clause.

The method signature is:

int  update($data, array|string $where)

Usually you will call the method like this:

$table = new Bugs();

$data = array(
    'updated_on'      => '2007-03-23',
    'bug_status'      => 'FIXED'
);

$where = $table->getAdapter()->quoteInto('bug_id = ?', 1234);

$table->update($data, $where);

quoteInto is just going to escape the variable, not bind it.

There needs to be a way to use bind variables, otherwise a DBMS is not going to cache this query effectivly.

Am I missing something, or is this an oversight on Zend's part?

Solution

You are only updating data, RDBMS (I assume MySQL) doesn't cache UPDATE queries. If you still want to use bind variables (security? performance?), you will have to use prepared statements:

$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$stmt = $db->prepare("UPDATE table SET key = :key, value = :value");

foreach ($data as $key=>$value) {
    $stmt->bindParam('key', $key);
    $stmt->bindParam('value', $value);
    $stmt->execute();
}

But unless you are having millions of UPDATE queries in a batch I don't think you should bother with this. Just use the $table->update($data, $where);