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

asgeo1 picture asgeo1 · Oct 22, 2009 · Viewed 10.5k times · Source

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?

Answer

bas picture bas · Oct 23, 2009

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