This article mainly introduces the Zend Framework database operation skills, and summarizes and analyzes the common functions, common operations and related precautions of Zend Framework for database operations in the form of examples. Friends in need can refer to this article
Examples summarize Zend Framework database operations. Share it with everyone for your reference, the details are as follows:
Zend_Db database knowledge
Example:
Model file:
$this->fetchAll("is_jian=1","id DESC",0,2)->toArray();
//根据is_jian=1，按id倒序排列取前2条记录当第一个参数为null时，则直接按id倒序排列ASC为正序。Copy after login
Routing file:
$video=new Video();//实例化数据库类
$this->view->get2Video =$video->get2Video();//取到2条首页推荐的数据
Copy after login
index.phtml file:
<?php foreach ($this->get2Video as $video): ?>
<?=$video['id']; ?>
<?=$video['name']; ?>
<? endforeach; ?>
Copy after login
Add quotes to prevent database attacks
quote usage
$value = $db->quote('St John"s Wort');
// $value 现在变成了 '"St John\"s Wort"' (注意两边的引号)
// 为数组加引号
$value = $db->quote(array('a', 'b', 'c'));
// $value 现在变成了 '"a", "b", "c"' (","分隔的字符串)Copy after login
quoteInto usage
echo $where = $db->quoteInto('id = ?', 1);
// $where 现在为 'id = "1"' (注意两边的引号)
// 在where语句中为数组加上引号
$where = $db->quoteInto('id IN(?)', array(1, 2, 3));
// $where 现在为 'id IN("1", "2", "3")' (一个逗号分隔的字符串)Copy after login
(1)Data query summary
Query directly. (Use complete sql statement)
//function quoteInto($text, $value, $type = null, $count = null)
$db = $this->getAdapter();
$sql = $db->quoteInto('SELECT * FROM `m_video` WHERE `is_guo` =?', '1');
$result = $db->query($sql);
// 使用PDOStatement对象$result将所有结果数据放到一个数组中
$videoArray = $result->fetchAll();
Copy after login
fetchAll usage
fetchAll($where = null, $order = null , $count = null, $offset = null)
Retrieve the values of all fields in the result set and return them as a continuous array. If the parameter is not set, write it as null
can be retrieved The specified number of results in the result set
$videoArray=$this->fetchAll("is_jian=1 and is_guo=1","id DESC",0,2)->toArray();Copy after login
fetchAssoc usage
##fetchAssoc($sql, $bind = array())
Retrieve the values of all fields in the result set as an associative array, with the first field as the code
$db = $this->getAdapter();
$videoArray=$db->fetchAssoc("SELECT * FROM m_video WHERE `is_jian` = :title",array('title' => '1'));Copy after login
fetchCol usage
fetchCol($sql, $bind = array())
Get the first field name of all result rows
$db = $this->getAdapter();
$videoArray=$db->fetchCol("SELECT name FROM m_video WHERE `is_jian` = :title",array('title' => '1'));Copy after login
fetchOne usage
fetchOne($sql, $bind = array())
Only retrieve the first field value
$db = $this->getAdapter();
echo $videoArray=$db->fetchOne("SELECT count(*) FROM m_video WHERE `is_jian` = :title",array('title' => '1'));Copy after login
fetchPairs usage
fetchPairs($sql, $bind = array())
Retrieve a related array, the first field value is the code (id), the second field is the value (name )
Return: Array([1] => Zodiac Romance [2] => Romance), 1,2: are the id fields
$db = $this->getAdapter();
$videoArray=$db->fetchPairs("SELECT id, name FROM m_video WHERE is_jian = :title",array('title' => '1'));Copy after login
fetchRow usage
fetchRow($where = null, $order = null)
Only retrieve the first row of the result set
$videoArray=$this->fetchRow("is_jian=1 and is_guo=1", 'id DESC')->toArray();Copy after login
query usage
//function query($sql, $bind = array())
$db = $this->getAdapter();
$result = $db->query('SELECT * FROM `m_video`');
//$result = $db->query('SELECT * FROM `m_video` WHERE `name` = ? AND id = ?',array('十二生肖奇缘', '1'));
//$result->setFetchMode(Zend_Db::FETCH_OBJ);//FETCH_OBJ为默认值,FETCH_NUM,FETCH_BOTH
//while ($row = $result->fetch()) {
// echo $row['name'];
//}
//$rows = $result->fetch();
//$rows = $result->fetchAll();
//$obj = $result->fetchObject();//echo $obj->name;
// echo $Column = $result->fetchColumn(0);//得到结果集的第一个字段，比如0为id号,用于只取一个字段的情况
print_r($rows);Copy after login
select usage
$db = $this->getAdapter();
$select = $db->select();
$select->from('m_video', array('id','name','clicks'))
->where('is_guo = :is_guo and name = :name')
->order('name')// 按什么排序列，参加为数组(多个字段)或字符串(一个字段)
->group()//分组
->having()//分组查询数据的条件
->distinct()// 无参数，去掉重复的值。有时候与groupby返回的结果一样
->limit(10);
// 读取结果使用绑定的参数
$params = array('is_guo' => '1','name'=>'十二生肖奇缘');
//$sql = $select->__toString();//得到查询语句，可供调试
$result = $db->fetchAll($select,$params);
执行select的查询
$stmt = $db->query($select);
$result = $stmt->fetchAll();
Copy after login
or use
$stmt = $select->query();
$result = $stmt->fetchAll();
Copy after login
If you use
$db->fetchAll($select)
Copy after login
directly, the result will be the same
Usage of multi-table joint query
$db = $this->getAdapter();
$select = $db->select();
$select->from('m_video', array('id','name','pic','actor','type_id','up_time'))
->where('is_guo = :is_guo and is_jian = :is_jian')
->order('up_time')
->limit(2);
$params = array('is_guo' => '1','is_jian'=>'1');
$select->join('m_type', 'm_video.type_id = m_type.t_id', 'type_name');//多表联合查询
$videoArray = $db->fetchAll($select,$params);
Copy after login
find() method, you can use the primary key value to retrieve data in the table.
// SELECT * FROM round_table WHERE id = "1"
$row = $table->find(1);
// SELECT * FROM round_table WHERE id IN("1", "2", 3")
$rowset = $table->find(array(1, 2, 3));Copy after login
(2) Data deletion summary
The first method: you can delete any table
//quoteInto($text, $value, $type = null, $count = null)
$table = 'm_video';// 设定需要删除数据的表
$db = $this->getAdapter();
$where = $db->quoteInto('name = ?', 'ccc');// 删除数据的where条件语句
echo $rows_affected = $db->delete($table, $where);// 删除数据并得到影响的行数
Copy after login
The second method: you can only delete this table
//delete用法
// delete($where)
$where = "name = 'bbb'";
echo $this->delete($where);// 删除数据并得到影响的行数
Copy after login
(3) Data update summary
The first method: you can update any table
// 以"列名"=>"数据"的格式构造更新数组,更新数据行
$table = 'm_video';// 更新的数据表
$db = $this->getAdapter();
$set = array (
'name' => '蝶影重重',
'clicks' => '888',
);
$where = $db->quoteInto('id = ?', '10');// where语句
// 更新表数据,返回更新的行数
echo $rows_affected = $db->update($table, $set, $where);
Copy after login
Second method: Only
$set = array (
'name' => '蝶影重重22',
'clicks' => '8880',
);
$db = $this->getAdapter();
$where = $db->quoteInto('id = ?', '10');// where语句
$rows_affected = $this->update($set, $where);// 更新表数据,返回更新的行数
Copy after login
(4) Data insertion summary## can be updated in this table
#The first method: you can insert data into any table
$table = 'm_gao';// 插入数据的数据表
$db = $this->getAdapter();
// 以"列名"=>"数据"的格式格式构造插入数组,插入数据行
$row = array (
'title' => '大家好。111',
'content' => '影视网要改成用zend framework开发啊',
'time' => '2009-05-04 17:23:36',
);
// 插入数据行并返回插入的行数
$rows_affected = $db->insert($table, $row);
// 最后插入的数据id
echo $last_insert_id = $db->lastInsertId();
$row=array(
'name'=>'curdate()',
'address' => new Zend_Db_Expr ('curdate()')
)
Copy after login
In this way, the subfield name will insert a curdate() character string, and address inserts a time value (the result of curdate() 2009-05-09)
The second method: it can only be suitable for the ones in this table and has not been summarized
(5)Transaction processing
$table = 'm_gao';// 插入数据的数据表
$db = $this->getAdapter();
$db->beginTransaction();//Zend_Db_Adapter会回到自动commit模式下，直到你再次调用 beginTransaction()方法
// 以"列名"=>"数据"的格式格式构造插入数组,插入数据行
$row = array (
'id'=>null,
'title' => '大家好。111',
'content' => '影视网要改成用zend framework开发啊',
'time' => '2009-05-04 17:23:36',
);
try {
// 插入数据行并返回插入的行数
$rows_affected = $db->insert($table, $row);
// 最后插入的数据id
$last_insert_id = $db->lastInsertId();
$db->commit();// 事务提交
}catch (Exception $e){
$db->rollBack();
echo '捕获异常：'.$e->getMessage();//打出异常信息
}
echo $last_insert_id;Copy after login
(6)Others
$db = $this->getAdapter();
$tables = $db->listTables(); //列出当前数据库中的所有表
$fields = $db->describeTable('m_video');//列出一个表的字段情况
Copy after login
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
