zend DB 常用動作

直接對Zend_Db操作

• 新增DB連線
  

  $db = Zend_Db::factory('Pdo_Mysql', array(
  'host'     => '127.0.0.1',
  'username' => 'webuser',
  'password' => 'xxxxxxxx',
  'dbname'   => 'course'
  ));

• 下sql語法
  

  $stmt = $db->query('SELECT * FROM course');
  
  //Zend_Db提供fecth()，可抓出row object
  while($row = $stat->fetch()){
      echo $row['name'];
  }

• 替換sql語法裡的參數
  

  $sql = 'SELECT * FROM course WHERE nam = ? AND status = ?';
  $stmt = new Zend_Db_Statement_Mysqli($db, $sql); //mysql就用mysqli
  $stmt->execute(array('demo course','display'));

宣告多個DB

• 宣告(configs/application.ini)
  

  //db1
  resources.multidb.course.adapter = "PDO_MYSQL"
  resources.multidb.course.host = "localhost"
  resources.multidb.course.username = "root"
  resources.multidb.course.password = "ok1234"
  resources.multidb.course.dbname = "course"
  resources.multidb.course.driver_options.1002 = "SET NAMES utf8"
  resources.multidb.teegle.default = true
  
  //db2
  resources.multidb.admin.adapter = "PDO_MYSQL"
  resources.multidb.admin.host = "localhost"
  resources.multidb.admin.username = "root"
  resources.multidb.admin.password = "ok1234"
  resources.multidb.admin.dbname = "admin"
  resources.multidb.admin.driver_options.1002 = "SET NAMES utf8"

• 選擇db
  

  $resource = $this->PluginResource('multidb');
  $resource->init();
  $db = $resource->getDb('course'); //取得course db
  $db->select()
      ->from( ... ) //多個from
      ->where( ... );

對Zend_Db_Table下指令

• 設定table
  

  Zend_Db_Table::setDefaultAdapter($db);
  $courseTable = new Zend_Db_Table('course');

• 在fetchAll裡加參數
  

  $resultSet  = $table->fetchAll(
          $table->select()
             ->where('status = ?', 'NEW') //有多個where就再下一次where(xx)
             ->order('id ASC')
             ->limit(10, 0)
  );
  
  //塞進array
  $entries   = array();
  foreach ($resultSet as $row) {   
      $object = row->id; // or row['id'];
      $entries[] = $object;
  }

• 讓DbTable物件 讀非預設DB
  DbTable會預設讀config裡設定的"db"
  如果要讀一個db怎麼辦咧？
  1.註冊db
  在Bootstrap.php裡，註冊想要讀的db
  

  //application/Bootstrap.php
  class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
  {
      $resource = $this->getPluginResource('multidb'); //假設存放在multidb裡,參考如何設定多個db
      $resource->init(); //初始化，不然不會動
  
      //註冊要讀取的DB - admin
      $admin = $resource->getDb('admin');
      Zend_Registry::set("admin", $admin);
  }

2.改寫DbTable的建構子 方法一 就在DbTable的class下,override construct 把要給DbTable的物件參數中的'db'改成目前要讀的db

class Application_Model_DbTable_User extends Zend_Db_Table_Abstract { 
    protected $_name = 'user'; 
    //方法一     
    public function __construct($config=array()){
        $this->read  = Zend_Registry::get('admin');
        $this->write = Zend_Registry::get('admin');
        $config['db'] = Zend_Registry::get('admin');                       
        return parent::__construct($config);
    }  

    //方法二  
    protected function __setupDatabaseAdapter(){ 
    } 
}

join

//Build this query: 
//  Select p."product_id",p."product_name", l.* 
//  From "products" AS p JOIN "line_items" AS l 
// ON p.product_id = l.product_id  
$select = $db->select()->from (array('p'=> 'products'), 
array('product_id','product_name'))->join(array('l' => 'line_items'), 'p.produt_id = l.product_id');

References
Zend Framework Certification Study Guide