[ZF]建立Database Table Model

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

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

0.資料庫schema及預設資料

CREATE TABLE IF NOT EXISTS `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `admin` (`id`, `account`, `password`, `status`) VALUES
(1, 'johnson', 'root123', 1);

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

#可以將production換成其他群組，例如：development、testing
zf configure db-adapter 'adapter=PDO_MYSQL&dbname="test"&host="localhost"&username="root"&password="123"' production

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

resources.db.adapter = "PDO_MYSQL"
resources.db.params.dbname = "test"
resources.db.params.host = "localhost"
resources.db.params.username = "root"
resources.db.params.password = "123"
;這行必須手動加進去，將語系設成utf8
resources.db.params.driver_options.1002 = "SET NAMES utf8"

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

#Admin為Table物件名稱，admin為資料表名稱
zf create db-table Admin admin

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

class Application_Model_DbTable_Admin extends Zend_Db_Table_Abstract
{
    protected $_name = 'admin';
}

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

zf create model AdminRow

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

class Application_Model_AdminRow
{
    protected $_id;
    protected $_account;
    protected $_password;
    protected $_status;

    public function __construct($options = null)
    {
        if (is_array($options)) {
            $this->setOptions($options);
        }
    }

    public function __set($name, $value)
    {
        $method = 'set' . ucfirst($name);
        if (!method_exists($this, $method)) {
            throw new Exception('Invalid property');
        }
        $this->$method($value);
    }

    public function __get($name)
    {
        $method = 'get' . ucfirst($name);
        if (!method_exists($this, $method)) {
            throw new Exception('Invalid property');
        }
        return $this->$method();
    }

    public function setOptions($options)
    {
        $methods = get_class_methods($this);
        foreach ($options as $key => $value) {
            $method = 'set' . ucfirst($key);
            if (in_array($method, $methods)) {
                $this->$method($value);
            }
        }
        return $this;
    }

    public function setId($id)
    {
        $this->_id = $id;
        return $this;
    }

    public function setAccount($account)
    {
        $this->_account = $account;
        return $this;
    }

    public function setPassword($password)
    {
        $this->_password = $password;
        return $this;
    }

    public function setStatus($status)
    {
        $this->_status = $status;
        return $this;
    }

    public function getId()
    {
        return $this->_id;
    }

    public function getAccount()
    {
        return $this->_account;
    }

    public function getPassword()
    {
        return $this->_password;
    }

    public function getStatus()
    {
        return $this->_status;
    }
}

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

zf create model AdminMapper

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

class Application_Model_AdminMapper
{
    protected $_dbTable;
    //設定資料表物件
    public function setDbTable($dbTable)
    {
        if (is_string($dbTable)) {
            $dbTable = new $dbTable();
        }
        if (!$dbTable instanceof Zend_Db_Table_Abstract) {
            throw new Exception('Invalid table data gateway provided');
        }
        $this->_dbTable = $dbTable;
        return $this;
    }

    //取得資料表物件
    public function getDbTable()
    {
        if (null === $this->_dbTable) {
            $this->setDbTable('Application_Model_DbTable_Admin');
        }
        return $this->_dbTable;
    }

    //Insert and Update
    public function save($admin)
    {
        $data = array(
            'id'        => $admin->getId(),
            'account'   => $admin->getAccount(),
            'password' => $admin->getPassword(),
            'status' => $admin->getStatus(),
        );

        foreach ($data as $key => $val) {
            if (null === $val) {
                unset($data[$key]);
            }
        }

        if (null === ($id = $admin->getId())) {
            $this->getDbTable()->insert($data);
        } else {
            $this->getDbTable()->update($data, array('id = ?' => $id));
        }

    }

    //find ID(Primary Key)
    public function find($id)
    {
        $result = $this->getDbTable()->find($id);
        if (0 == count($result)) {
            return;
        }
        $row = $result->current();
        $admin = new Application_Model_AdminRow();
        $admin->setId($row->id)
              ->setAccount($row->account)
              ->setPassword($row->password)
              ->setStatus($row->status);
        return $admin;
    }

    //Search Account
    public function searchAccount($account)
    {
        if (null === $account) {
            return ;
        }
        $select = $this->getDbTable()->select()->where("account = ?",$account);
        return $this->fetchAll($select);
    }

    public function fetchAll($select = null)
    {
        //如果只抓單筆可以使用fetchRow($select)
        $resultSet = $this->getDbTable()->fetchAll($select);
        $entries   = array();
        foreach ($resultSet as $row) {
            $entry = new Application_Model_AdminRow();
            $entry->setId($row->id)
                  ->setAccount($row->account)
                  ->setPassword($row->password)
                  ->setStatus($row->status);
            $entries[] = $entry;
        }
        return $entries;
    }
}

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

$table_admin = new Application_Model_AdminMapper();

//Insert
$options = array(
	'account' => '這只是測試',
	'password' => '123',
	'status' => '2'
);
$table_admin->save(new Application_Model_AdminRow($options));

//Update
$options = array(
	'id' => '2',
	'password' => '456',
);
$table_admin->save(new Application_Model_AdminRow($options));



//SearchName
print_r($table_admin->searchAccount('Hello'));

//find
print_r($table_admin->find(1));

//All
print_r($result = $table_admin->fetchAll());