How to use bind variables with Zend_Db_Table-> update () in where clause
If I want to use a method Zend_Db_Table->update()
to update my table with data, I still cannot use bind variables in the where clause.
Method signature:
int update($data, array|string $where)
You usually call a 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
just going to avoid the variable, not bind it.
There must be a way to use the bind variables, otherwise the DBMS will not cache this query efficiently.
Am I missing something, or is this an oversight of the Zend part?
source to share
You are only updating data, the RDBMS (I assume MySQL) does not 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 have millions of UPDATE queries in the batch, I don't think you should worry about that. Just use $ table-> update ($ data, $ where);
source to share