Php/zend framework/DBモデルメモ - 変更履歴

Admin: /* class外部からtable名を変更する */

2025-08-26T14:49:20Z

‎class外部からtable名を変更する

Admin: /* データ更新 */

2025-07-27T15:54:24Z

‎データ更新

Admin: /* Zend_Db_SelectでLeftJoin */

2023-06-23T09:43:47Z

‎Zend_Db_SelectでLeftJoin

Admin: /* Zend_Db_SelectでLeftJoin */

2023-06-23T09:42:27Z

‎Zend_Db_SelectでLeftJoin

Admin: /* sqlログを吐く */

2023-06-23T09:39:28Z

‎sqlログを吐く

Admin: /* Zend_Db_Tableで集計SQLを実行した例 */

2021-12-11T16:03:27Z

‎Zend_Db_Tableで集計SQLを実行した例

Admin: /* class外部からtable名を変更する */

2016-03-14T21:34:18Z

‎class外部からtable名を変更する

2016年3月14日 (月) 05:27にAdminによる

2016-03-14T05:27:21Z

127.0.0.1: ページの作成:「==モデルの定義== application/models/dao/TestDao.phpに以下を定義 class TestDao extends Zend_Db_Table_Abstract { // テーブル名 protected $_name = '...」

2015-05-19T17:59:12Z

ページの作成:「==モデルの定義== application/models/dao/TestDao.phpに以下を定義 class TestDao extends Zend_Db_Table_Abstract { // テーブル名 protected $_name = '...」

新規ページ

==モデルの定義==
application/models/dao/TestDao.phpに以下を定義
class TestDao extends Zend_Db_Table_Abstract
{
// テーブル名
protected $_name = 'test';
// 主キー
protected $_primary = 'id';
}

==データ挿入==
create table test(id int, title varchar(10));なテーブルに挿入
$db = Zend_Db::factory('PDO_MYSQL', $db_info);
// すべての Zend_Db_Table オブジェクトに対するデフォルトアダプタを設定します
Zend_Db_Table::setDefaultAdapter($db);
$dao = new TestDao();
$item = array(
'id' => 1,
'title' => 'abc',
);
// 実行&プライマリーID取得
$id = $dao->insert($item);

==データ削除==
$where = $dao->getAdapter()->quoteInto('id = ?', 1, 'INTEGER');
$dao->delete($where);

==データ更新==
$item = array('title' => 'def');
$where = $dao->getAdapter()->quoteInto('id = ?', 1, 'INTEGER');
$dao->update($item, $where);

==日時を自動で入れる==
public function insert($data = array())
{
$data['created'] = date('Y-m-d H:i:s', time());
parent::insert($data);
}
==検索==
$rows = $dao->find(1);
$rowArray = $rows->toArray();

==検索カスタマイズ==
TestDaoクラスに以下メソッドを追加し
public function findByTitle($title)
{
$where = $this->getAdapter()->quoteInto('title = ?', $title);
return $this->fetchAll($where);
}
アクションに以下を追加
$rows = $dao->findByTitle('aiueo');

==findで一行だけ取得する方法==
$row = $users->find(1)->current();

==トランザクション==
$dao->getAdapter()->beginTransaction();
$dao->getAdapter()->commit();
$dao->getAdapter()->rollback();

==検索推奨メソッド名==
タイトルで検索する場合
$dao->findAllByTitle($title);

IDとパスワードで検索
$dao->findByIdAndPassword($id, $password);

==複数のDBを使用する場合==
$db = Zend_Db::factory('PDO_MYSQL', $config->db->params);
Zend_Registry::set('my_db', $db);
$dao = new Model_Dbtable_Users(array('db' => 'my_db'));

==テーブル名を動的に変更する場合==
public function __construct($month)
{
$this->_name = 'tests' . $month;
parent::__construct();
}

==likeを使った条件==
$where = $adapter->quoteInto('word like ?', '%' . $word . '%');

==CreateTable文取得==
public function showCreateTable()
{
$adapter = $this->getAdapter();
$sql = sprintf('SHOW CREATE TABLE %s;', $adapter->quoteIdentifier($this->_name));
$stmt = $adapter->query($sql);
$row = $stmt->fetch(Zend_Db::FETCH_ASSOC);
return $row['Create Table'];
}

==DropTable文取得==
public function getDropTable()
{
$adapter = $this->getAdapter();
$sql = sprintf('DROP TABLE IF EXISTS %s;', $adapter->quoteIdentifier($this->_name));
return $sql;
}

==Zend_Db_Tableで集計SQLを実行した例==
public function countGroupModeAndServiceCodeByCreated($date)
{
$adapter = $this->getAdapter();
$select = $adapter->select();
$select->from($this->_name,
'left(created, 10) as day, mode, service_code, count(*) as cnt',
$this->_schema);
$select->where($adapter->quoteInfo('left(created, 10) = ?', $date));
$select->group('left(created, 10), mode, service_code');
return $adapter->fetchAll($select);
}
==Zend_Db_Tableでcountを使う例==
$adapter = $this->getAdapter();
$select = $adapter->select();
$select->from($this->_name, 'count(*)', $this->_schema);
$select->where($adapter->quoteInto("id = ?", $id, 'INTEGER'));
return $adapter->fetchOne($select);

==Zend_Db_Tableでテーブル件数取得（Zend_Db_Tableで使うことは推奨ではない==
$adapter = $this->getAdapter();
$select = $adapter->select();
$select->from($this->_name, 'count(*)');
return $adapter->fetchOne($select);

==Zend_Db_TableでIN検索==
if ($ids) {
$where = $this->getAdapter()->quoteInto("id IN (?)", $ids);
return $adapter->fetchAll($where);
}

==Zend_Db_TableでOR検索==
public function fetchAllByIds($ids)
{
$adapter = $this->getAdapter();
$select = $adapter->select();
$select->from($this->_name, "*", $this->_schema);
foreach ($ids as $id) {
$select->orWhere($adapter->quoteInto("id = ?", $id, 'INTEGER'));
}
return $adapter->fetchAll($select);
}

==Zend_Db_TableでNullを入れる==
$data = array(
'flag' => null // 普通にNullでいける
);
$this->update($data);

$dao->insert($data);

==Zend_Db_Tableごとに固有のDBを指定する==
class TestDao extends Zend_Db_Table_Abstract
{
protected $_name = 'test';
public function _setup()
{
$config = new Zend_Config_Ini(APPLICATION_PATH . '/configs/db.ini', APPLICATION_ENV);
$adapter = Zend_Db::factory($config->default);
$this->_setAdapter($adapter);
}

==SQL式評価==
例：update間隔をSQLを使って設定
$data = array();
$data['updated_span'] = new Zend_Db_Expr('UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(updated)');
$data['updated'] = date('Y-m-d H:i:s', time());
$this->update($data);

例：increment処理などはこのように
$adapter = $this->getAdapter();
$data['access'] = new Zend_Db_Expr('access + 1');
$where = $adapter->quoteInto("id = ?", $id);
return $this->update($data, $where);

==sqlログを吐く==
注：プレースホルダーとバインド値は個別に出力
*Bootstarap.php
$config = new Zend_Config_Ini('db.ini', 'development');
$db = Zend_Db::factory($config->db);
// プロファイルを有効に
$db->getProfiler()->setEnabled(true);
Zend_Db_Table_Abstract::setDefaultAdapter($db);

*AbstractDao.php
require_once 'Zend/Db/Table.php';
require_once 'Zend/Log.php';
require_once 'Zend/Log/Writer/Stream.php';
class AbstractDao extends Zend_Db_Table_Abstract
{
protected function _update($data, $where)
{
$log = new Zend_Log();
$log->addWriter(new Zend_Log_Writer_Stream('/tmp/sql.log'));
try {
$this->update($data, $where);
$query = $this->_db->getProfiler()->getLastQueryProfile();
$sqllog = sprintf("%s [%s] %ss", $query->getQuery(), join(",", $query->getQueryParams()), $query->getElapsedSecs());
$log->log($sqllog, Zend_Log::DEBUG);
} catch(Exception $e) {
$query = $this->_db->getProfiler()->getLastQueryProfile();
$sqllog = sprintf("%s [%s]", $query->getQuery(), join(",", $query->getQueryParams()));
$log->log($sqllog, Zend_Log::ERR);
throw $e;
}
}
}

==参考==
http://framework.zend.com/manual/ja/zend.db.table.html

@@ 行170: / 行170: @@
   $dao = new TestsDao();
   $dao->setOptions(array('name' => 'tests_1', 'schema' => 'schema_1'));
 ==SQL式評価==

@@ 行219: / 行219: @@
 $select->joinLeft($joinTableScheme.'.'.$joinTable, 'users.id = '. $joinTable.'.user_id', '*');
 </pre>
 ==Zend_Db_Selectで順序変更==
 <pre>

@@ 行218: / 行218: @@
 $select->from($this->_name, '*', $this->_schema);
 $select->joinLeft($joinTableScheme.'.'.$joinTable, 'users.id = '. $joinTable.'.user_id', '*');
 </pre>
 ==参考==
 http://framework.zend.com/manual/ja/zend.db.table.html

@@ 行163: / 行163: @@
 ==class外部からtable名を変更する==
   $dao = new TestsDao();
-  $dao->setOptions(array('name' => 'tests_tmp'));
+  $dao->setOptions(array('name' => 'tests_1', 'schema' => 'schema_1'));
 ==SQL式評価==

@@ 行30: / 行30: @@
   $where = $dao->getAdapter()->quoteInto('id = ?', 1, 'INTEGER');
   $dao->update($item, $where);
 ==日時を自動で入れる==

@@ 行107: / 行107: @@
                    'left(created, 10) as day, mode, service_code, count(*) as cnt',
                    $this->_schema);
-    $select->where($adapter->quoteInfo('left(created, 10) = ?', $date));
+    $select->where($adapter->quoteInto('left(created, 10) = ?', $date));
     $select->group('left(created, 10), mode, service_code');
     return $adapter->fetchAll($select);
+  }
 ==Zend_Db_Tableでcountを使う例==
   $adapter = $this->getAdapter();

@@ 行160: / 行160: @@
           $this->_setAdapter($adapter);
+      }
 ==SQL式評価==