[ZF]建立Database Table Model

Published by johnson on

在ZendFramework中，可以將每張資料表的控制寫成Model，這樣的好處是可以讓Controller更為乾淨，也可以避免直接撰寫sql語法

如果透過zf tools建立整個流程會方便許多，以下範例皆是利用zf tools建立

0.資料庫schema及預設資料

顯示原始碼
列印？
1CREATE TABLE IF NOT EXISTS `admin` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `account` varchar(50) NOT NULL,
4  `password` varchar(50) NOT NULL,
5  `status` int(11) NOT NULL,
6  PRIMARY KEY (`id`)
7) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
8 
9INSERT INTO `admin` (`id`, `account`, `password`, `status`) VALUES
10(1, 'johnson', 'root123', 1);

1.資料庫設定
利用zf tools建立

顯示原始碼
列印？
1#可以將production換成其他群組，例如：development、testing
2zf configure db-adapter 'adapter=PDO_MYSQL&dbname="test"&host="localhost"&username="root"&password="123"' production

完成後應該可以看到application/configs/application.ini

顯示原始碼
列印？
1resources.db.adapter = "PDO_MYSQL"
2resources.db.params.dbname = "test"
3resources.db.params.host = "localhost"
4resources.db.params.username = "root"
5resources.db.params.password = "123"
6;這行必須手動加進去，將語系設成utf8
7resources.db.params.driver_options.1002 = "SET NAMES utf8"

2.建立Table物件
利用zf tools建立

顯示原始碼
列印？
1#Admin為Table物件名稱，admin為資料表名稱
2zf create db-table Admin admin

如果成功在application/models/DbTable會建立出一個Admin.php，並會有以下內容

顯示原始碼
列印？
1class Application_Model_DbTable_Admin extends Zend_Db_Table_Abstract
2{
3    protected $_name = 'admin';
4}

3.建立Table Row Model
利用zf tools建立

顯示原始碼
列印？
1zf create model AdminRow

成功的話路徑會是application/models/AdminRow.php，Row Model主要定義Row的資料和控制方法

顯示原始碼
列印？
1class Application_Model_AdminRow
2{
3    protected $_id;
4    protected $_account;
5    protected $_password;
6    protected $_status;
7 
8    public function __construct($options = null)
9    {
10        if (is_array($options)) {
11            $this->setOptions($options);
12        }
13    }
14 
15    public function __set($name, $value)
16    {
17        $method = 'set' . ucfirst($name);
18        if (!method_exists($this, $method)) {
19            throw new Exception('Invalid property');
20        }
21        $this->$method($value);
22    }
23 
24    public function __get($name)
25    {
26        $method = 'get' . ucfirst($name);
27        if (!method_exists($this, $method)) {
28            throw new Exception('Invalid property');
29        }
30        return $this->$method();
31    }
32 
33    public function setOptions($options)
34    {
35        $methods = get_class_methods($this);
36        foreach ($options as $key => $value) {
37            $method = 'set' . ucfirst($key);
38            if (in_array($method, $methods)) {
39                $this->$method($value);
40            }
41        }
42        return $this;
43    }
44 
45    public function setId($id)
46    {
47        $this->_id = $id;
48        return $this;
49    }
50 
51    public function setAccount($account)
52    {
53        $this->_account = $account;
54        return $this;
55    }
56 
57    public function setPassword($password)
58    {
59        $this->_password = $password;
60        return $this;
61    }
62 
63    public function setStatus($status)
64    {
65        $this->_status = $status;
66        return $this;
67    }
68 
69    public function getId()
70    {
71        return $this->_id;
72    }
73 
74    public function getAccount()
75    {
76        return $this->_account;
77    }
78 
79    public function getPassword()
80    {
81        return $this->_password;
82    }
83 
84    public function getStatus()
85    {
86        return $this->_status;
87    }
88}

4.建立Table Mapper
利用zf tools建立

顯示原始碼
列印？
1zf create model AdminMapper

成功的話路徑會是application/models/AdminMapper.php
在開發時主要使用此物件操作資料表，因此這個物件的方法就是隨著操作資料庫的需求自行撰寫

顯示原始碼
列印？
1class Application_Model_AdminMapper
2{
3    protected $_dbTable;
4    //設定資料表物件
5    public function setDbTable($dbTable)
6    {
7        if (is_string($dbTable)) {
8            $dbTable = new $dbTable();
9        }
10        if (!$dbTable instanceof Zend_Db_Table_Abstract) {
11            throw new Exception('Invalid table data gateway provided');
12        }
13        $this->_dbTable = $dbTable;
14        return $this;
15    }
16 
17    //取得資料表物件
18    public function getDbTable()
19    {
20        if (null === $this->_dbTable) {
21            $this->setDbTable('Application_Model_DbTable_Admin');
22        }
23        return $this->_dbTable;
24    }
25 
26    //Insert and Update
27    public function save($admin)
28    {
29        $data = array(
30            'id'        => $admin->getId(),
31            'account'   => $admin->getAccount(),
32            'password' => $admin->getPassword(),
33            'status' => $admin->getStatus(),
34        );
35 
36        foreach ($data as $key => $val) {
37            if (null === $val) {
38                unset($data[$key]);
39            }
40        }
41 
42        if (null === ($id = $admin->getId())) {
43            $this->getDbTable()->insert($data);
44        } else {
45            $this->getDbTable()->update($data, array('id = ?' => $id));
46        }
47 
48    }
49 
50    //find ID(Primary Key)
51    public function find($id)
52    {
53        $result = $this->getDbTable()->find($id);
54        if (0 == count($result)) {
55            return;
56        }
57        $row = $result->current();
58        $admin = new Application_Model_AdminRow();
59        $admin->setId($row->id)
60              ->setAccount($row->account)
61              ->setPassword($row->password)
62              ->setStatus($row->status);
63        return $admin;
64    }
65 
66    //Search Account
67    public function searchAccount($account)
68    {
69        if (null === $account) {
70            return ;
71        }
72        $select = $this->getDbTable()->select()->where("account = ?",$account);
73        return $this->fetchAll($select);
74    }
75 
76    public function fetchAll($select = null)
77    {
78        //如果只抓單筆可以使用fetchRow($select)
79        $resultSet = $this->getDbTable()->fetchAll($select);
80        $entries   = array();
81        foreach ($resultSet as $row) {
82            $entry = new Application_Model_AdminRow();
83            $entry->setId($row->id)
84                  ->setAccount($row->account)
85                  ->setPassword($row->password)
86                  ->setStatus($row->status);
87            $entries[] = $entry;
88        }
89        return $entries;
90    }
91}

5.測試
在Controller中就可以測試是否成功連結

顯示原始碼
列印？
1$table_admin = new Application_Model_AdminMapper();
2 
3//Insert
4$options = array(
5    'account' => '這只是測試',
6    'password' => '123',
7    'status' => '2'
8);
9$table_admin->save(new Application_Model_AdminRow($options));
10 
11//Update
12$options = array(
13    'id' => '2',
14    'password' => '456',
15);
16$table_admin->save(new Application_Model_AdminRow($options));
17 
18 
19 
20//SearchName
21print_r($table_admin->searchAccount('Hello'));
22 
23//find
24print_r($table_admin->find(1));
25 
26//All
27print_r($result = $table_admin->fetchAll());
Categories: ZendFramework

Related Posts

ZendFramework

[ZF]Zend_Db_Table

Zend Framework 中的 Zend_Db 可以針對資料庫操作 Read more…

ZendFramework

[ZF]View Helper – Partial

一般在操作 View 時，可能會有一些重複的結構需要render，這種 Read more…

ZendFramework

[ZF]Layout variables

在 Zend 裡面，除了 view 可以傳入變數之外，layout 也 Read more…