Zend Framework database summary [original]_PHP tutorial

Zend_Db database knowledge
Example:
Model file:
$this->fetchAll("is_jian=1","id DESC",0,2)->toArray();//According to is_jian=1, get the first 2 records in reverse order by id as the first parameter When it is null, ASC will be sorted directly by ID in reverse order into positive order.
Routing file:
$video=new Video();//instantiate database class
$this->view->get2Video =$video->get2Video();//Get 2 recommended data on the homepage
index.phtml file:
get2Video as $video): ?>



Add quotes to prevent database attacks
quote usage
$value = $db->quote(St John"s Wort);
// $value now becomes "St John"s Wort" (note the quotes on both sides)
// Add quotes to the array
$value = $db->quote(array(a, b, c));
// $value now becomes "a", "b", "c" ("," separated strings)
quoteInto usage
echo $where = $db->quoteInto(id = ?, 1);
// $where is now id = "1" (note the quotes on both sides)
// Add quotes to the array in the where statement
$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
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);
// Use 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 the values ​​of all fields in the result set and return them as a continuous array. If the parameters are not set, write null
The specified number of items in the result set can be retrieved
$videoArray=$this->fetchAll("is_jian=1 and is_guo=1","id DESC",0,2)->toArray();

fetchAssoc usage
fetchAssoc($sql, $bind = array())
Retrieve the values ​​of all fields in the result set and return them as an associative array. The first field is used 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())
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));

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

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)
Returns: 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));

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(zodiac romance, 1));
//$result->setFetchMode(Zend_Db::FETCH_OBJ);//FETCH_OBJ is the default value, 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 fetching only 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)//How to sort the columns, join as array (multiple fields) or string (one field)
->group()//Group
->having()//Conditions for group query data
->distinct()// No parameters, remove duplicate values. Sometimes the results returned by groupby are the same
->limit(10);
// Read the result using the bound parameters
$params = array(is_guo => 1,name=>The Romance of the Chinese Zodiac);
//$sql = $select->__toString();//Get the query statement for debugging
$result = $db->fetchAll($select,$params);
Execute select query
$stmt = $db->query($select);

$result = $stmt->fetchAll();
Or use
$stmt = $select->query();
$result = $stmt->fetchAll();
If you use
directly $db->fetchAll($select) has the same result
Multi-table joint query usage
$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);//Multiple table joint query
$videoArray = $db->fetchAll($select,$params);
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));

(2) Data deletion summary
The first method: You can delete any table
//quoteInto($text, $value, $type = null, $count = null)    
$table = m_video;//Set the table to delete data
$db = $this->getAdapter();
$where = $db->quoteInto(name = ?, ccc);//Where conditional statement to delete data
echo $rows_affected = $db->delete($table, $where);//Delete data and get the number of affected rows
Second method: Only
in this table can be deleted ​​​​ //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: you can update any table
// Construct an update array in the format of "column name" => "data" and update the data row
$table = m_video;//Updated data table
$db = $this->getAdapter();
$set = array (
name => Butterfly shadows,
clicks => 888,
);
$where = $db->quoteInto(id = ?, 10);//where statement
//Update table data and return the number of updated rows
echo $rows_affected = $db->update($table, $set, $where);

Second method: Only
in this table can be updated $set = array (
name => Butterfly Shadow Heavy 22,
clicks => 8880,
);
$db = $this->getAdapter();
$where = $db->quoteInto(id = ?, 10);//where statement
$rows_affected = $this->update($set, $where);//Update table data and return the number of updated rows
(4) Summary of data insertion
The first method: you can insert data into any table
$table = m_gao;//data table to insert data
$db = $this->getAdapter();
// Construct the insertion array in the format of "column name" => "data" and insert the data row
$row = array (
title => Hello everyone. 111,
content => The film and television network needs to be developed using zend framework,
time => 2009-05-04 17:23:36,
);
//Insert data rows and return the number of inserted rows
$rows_affected = $db->insert($table, $