Summary of database operation skills in Zend Framework

本文实例总结了Zend Framework数据库操作。分享给大家供大家参考，具体如下：

Zend_Db数据库知识

例子：

Model文件:

$this->fetchAll("is_jian=1","id DESC",0,2)->toArray();
//根据is_jian=1，按id倒序排列取前2条记录当第一个参数为null时，则直接按id倒序排列ASC为正序。

路由文件：

$video=new Video();//实例化数据库类
$this->view->get2Video =$video->get2Video();//取到2条首页推荐的数据

index.phtml文件：

<63;php foreach ($this->get2Video as $video):63;>
<63;=$video['id'];63;>
<63;=$video['name'];63;>
<63; endforeach;63;>

添加引号防止数据库攻击

quote用法

$value = $db->quote('St John"s Wort');
// $value 现在变成了 '"St John\"s Wort"' (注意两边的引号)
// 为数组加引号
$value = $db->quote(array('a', 'b', 'c'));
// $value 现在变成了 '"a", "b", "c"' (","分隔的字符串)

quoteInto用法

echo $where = $db->quoteInto('id = '?'; 1);
// $where 现在为 'id = "1"' (note the quotes on both sides)"}}
// quote the array in the where clause with quotes
$where = $db->quoteInto('id IN(?)', array(1, 2, 3));
// $where is now 'id IN("1", "2", "3")' (a comma-separated string)

(1)Data Query Summary

perform a direct query.　　(Use a 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);
// use the PDOStatement object $result to put all result data into an array
$videoArray = $result->fetchAll();

fetchAll usage

fetchAll($where = null, $order = null, $count = null, $offset = null)

retrieve all field values in the result set, return as a continuous array, if the parameter is not set, it is written as null

can retrieve a specified number of rows from the result set

$videoArray=$this->fetchAll("is_jian=1 and is_guo=1","id DESC",0,2)->toArray();

fetchAssoc usage

fetchAssoc($sql, $bind = array())

retrieve all field values in the result set, return as an associative array, the first field as the code

$db = $this->getAdapter();
$videoArray=$db->fetchAssoc("SELECT * FROM m_video WHERE `is_jian` = :title",array('title' => '1');

fetchCol usage

fetchCol($sql, $bind = array())

retrieve the name of the first field of all result rows

$db = $this->getAdapter();
$videoArray=$db->fetchCol("SELECT name FROM m_video WHERE `is_jian` = :title",array('title' => '1');

fetchOne usage

fetchOne($sql, $bind = array())

only retrieve the value of the first field

$db = $this->getAdapter();
echo $videoArray=$db->fetchOne("SELECT count(*FROM m_video WHERE `is_jian` = :title",array('title' => '1');

fetchPairs usage

fetchPairs($sql, $bind = array())

Retrieve a related array, the first field value is code (id), the second field is value (name)

Return: Array( [1] => Twelve Animals: A Tale of Destiny [2] => Fortune of Peach Blossom),1,2: is the id field.

$db = $this->getAdapter();
$videoArray=$db->fetchPairs("SELECT id, name FROM m_video WHERE is_jian = :title",array('title' => '1');

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();

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('Twelve Animals: A Tale of Destiny', '1');
//$result->setFetchMode(Zend_Db::FETCH_OBJ);//FETCH_OBJ as default, 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);//Get the first field of the result set, for example, 0 is the id number, used for only taking one field
print_r($rows);

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')// Sort by what, participate as an array (multiple fields) or a string (a field)
->group()//Grouping
->having()//Condition for grouping query data
->distinct()// No parameters, remove duplicate values. Sometimes it's the same as the result returned by groupby
->limit(10);
// Read results using bound parameters
$params = array('is_guo' => '"1','name'=>'Twelve Zodiacs: The Legend');
//$sql = $select->__toString();//Get the query statement, which can be used for debugging
$result = $db->fetchAll($select,$params);
Execute the select query
$stmt = $db->query($select);
$result = $stmt->fetchAll();

or use

$stmt = $select->query();
$result = $stmt->fetchAll();

If used directly

$db->fetchAll($select)

The result is 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');//Multi-table joint query
$videoArray = $db->fetchAll($select,$params);

find() method, 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));

(2)Summary of data deletion

The first method: can delete any table

//quoteInto($text, $value, $type = null, $count = null)
$table = 'm_video';// Set the table that needs to be deleted data
$db = $this->getAdapter();
$where = $db->quoteInto('name = '?);// WHERE clause for deleting data
echo $rows_affected = $db->delete($table, $where);// Delete data and get the number of affected rows

The second method: only suitable for deleting the table

//delete usage
// delete($where)
$where = "name = 'bbb'";
echo $this->delete($where);// Delete data and get the number of affected rows

(3)Data update summary

The first method: can update any table

// Construct an update array in the format of 'column name' => 'data', and update a data row
$table = 'm_video';// Updated table data
$db = $this->getAdapter();
$set = array (
'name' => 'Butterfly Shadows',
'clicks' => '888',
);
$where = $db->quoteInto('id = '?10);// WHERE clause
// Update table data, return the number of rows updated
echo $rows_affected = $db->update($table, $set, $where);

The second method: only suitable for updating the table

$set = array (
'name' => 'Butterfly Shadows',22',
'clicks' => '8880,
);
$db = $this->getAdapter();
$where = $db->quoteInto('id = '?10);// WHERE clause
$rows_affected = $this->update($set, $where);// Update table data, return the number of rows updated

(4)Data insertion summary

The first method: can insert data into any table

$table = 'm_gao';// The data table for inserting data
$db = $this->getAdapter();
// Construct an insert array in the format of 'column name' => 'data', and insert a data row
$row = array (
'title'   => 'Hello everyone.',111',
'content' => 'The film website needs to be developed using zend framework',
'time' => '"}}'2009-05-04 17:23:36',
);
// Insert data rows and return the number of rows inserted
$rows_affected = $db->insert($table, $row);
// Last inserted data id
echo $last_insert_id = $db->lastInsertId();
$row = array(
'name' => 'curdate()',
'address' => new Zend_Db_Expr ('curdate()')
)

In this way, the field 'name' will insert a string of 'curdate()', and 'address' will insert a time value (the result of 'curdate()')2009-05-09)

The second method: only suitable for the table that has not been summarized yet

(5)Transaction processing

$table = 'm_gao';// The data table for inserting data
$db = $this->getAdapter();
$db->beginTransaction();//Zend_Db_Adapter will return to the auto-commit mode until you call the beginTransaction() method again
// Construct an insert array in the format of 'column name' => 'data', and insert a data row
$row = array (
'id' => null,
'title'   => 'Hello everyone.',111',
'content' => 'The film website needs to be developed using zend framework',
'time' => '"}}'2009-05-04 17:23:36',
);
try {
// Insert data rows and return the number of rows inserted
$rows_affected = $db->insert($table, $row);
// Last inserted data id
$last_insert_id = $db->lastInsertId();
$db->commit();// Transaction commit
}catch (Exception $e){
$db->rollBack();
echo 'Caught exception: $e'->getMessage();//Print exception information
}
echo $last_insert_id;

(6)other

$db = $this->getAdapter();
$tables = $db->listTables(); //List all tables in the current database
$fields = $db->describeTable('m_video');//List the field information of a table

Readers who are interested in more about zend-related content can check out the special topics on this site: 'Zend FrameWork Framework Tutorial', 'Summary of Excellent PHP Development Frameworks', 'Summary of Common Skills of Yii Framework', 'ThinkPHP Tutorial', 'PHP Object-Oriented Programming Tutorial', 'php'+mysql database operation tutorial and php common database operation skills summary

I hope this article is helpful to everyone in PHP program design based on the Zend Framework.

Statement: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume any relevant legal responsibility. If you find any content suspected of copyright infringement, please send an email to: notice#w3Please send an email to codebox.com (replace # with @ when sending an email) to report violations, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.