- 新增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(){
}
}//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');Zend Framework Certification Study Guide
