Our blog

 

Zend Framework – Reusing Prepared Zend_Db_Statements

If you use Zend_Db_Statements directly as well as using the Zend_Db_Table family of classes for Active Record et al then you might find the following little tip useful.

By default, if you run a query using something like

PLAIN TEXT
PHP:
  1. public function query($sql, $params=false) {
  2.     if(empty($params)) {
  3.         $stmt = $this->getAdapter()->query($sql);
  4.     }else {
  5.         $stmt = $this->getAdapter()->query($sql, $params);
  6.     }
  7.     return $stmt;
  8. }

then every time you call your query method, you will be preparing the statement again.

Of course that's not really a good idea if you are repeating the same query multiple times simply changing the parameters to be passed in.

In that kind of scenario you can do something like this:

PLAIN TEXT
PHP:
  1. $stmt = $db->query($sql, $params);
  2. $result1 = $stmt->fetch();
  3. $stmt->execute($params2);
  4. $result2 = $stmt->fetch();

Heres an example of a chunk of code that is working out a category path from an ecommerce system for a particular category id.

PLAIN TEXT
PHP:
  1. $stmt = $db->query("select c.parent_id, cd.categories_name from categories c join categories_description cd using(categories_id)
  2. where categories_id = ?", array($categories_id));
  3. while(false!==($r=$stmt->fetch())){
  4.     $categoryString[]=$r['categories_name'];
  5.     if($r['parent_id']==0){
  6.         break;
  7.     }
  8. //Note this bit - we are just re executing the statement with some new parameters.
  9.     $stmt->execute(array($r['parent_id']));
  10. }

This tip alone can add a lot of speed if you are doing repetitive statements

More Reading:

 

Leave a Reply