Zend Framework Database Operation Tips Summary

This article mainly introduces the Zend Framework database operation skills, combined with the example form summarizes the Zend Framework for database operation of the common functions, common operations and related considerations, the need for friends can refer to the following

This example summarizes the Zend Framework database operations. Share to everyone for your reference, as follows:

zend_db Database Knowledge

Example:

Model file:

$this->fetchall ("Is_jian=1", "id DESC", 0,2)->toarray ();//According to Is_jian=1, the first 2 records are sorted in reverse order The ASC is ordered directly by the reverse ID.

Routing files:

$video =new Video ();//Instantiate database class $this->view->get2video = $video->get2video ();//Fetch to 2 home recommended data

index.phtml file:

<?php foreach ($this->get2video as $video): ><?= $video [' id '];? ><?= $video [' name '];? ><? Endforeach;?>

Add quotation marks to prevent database attacks

Quote Usage

$value = $db->quote (' st John ' s wort ');//$value now becomes ' "St John\" s Wort "' (note the quotation marks on both sides)//Array Quotes $value = $db->quote (arra Y (' A ', ' B ', ' C '));//$value now becomes ' a ', ' B ', ' C ' ' ("," delimited string)

Quoteinto usage

echo $where = $db->quoteinto (' id =? ', 1);//$where is now ' id = ' 1 ' (note the quotation marks on both sides)//The array is quoted in the WHERE statement $where = $db->quotein To (' ID in (?) ', Array (1, 2, 3));//$where is now ' ID in (' 1 ', ' 2 ', ' 3 ') ' (a comma-delimited string)

(1) Data Query Summary

directly to the query. (using the full SQL statement)

function Quoteinto ($text, $value, $type = null, $count = null) $db = $this->getadapter (); $sql = $db->quoteinto (' SEL ECT * 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)

Retrieves the values of all fields in the result set, returns as a continuous array, and writes NULL if the parameter is not set

You can retrieve the specified number of bars for the result set

$videoArray = $this->fetchall ("Is_jian=1 and Is_guo=1", "id DESC", 0,2)->toarray ();

FETCHASSOC usage

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

Retrieves the values of all fields in the result set, returns as an associative array, and the first field as a 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 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())

Retrieve only 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 a code (ID), the second field is a value (name)

Returns: Array ([1] = 12 Zodiac [2] = Peach blossom), 1, 2: 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)

Retrieve only 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 (' 12 ', ' 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-& Gt;fetchall ();//$obj = $result->fetchobject ();//echo $obj->name;//echo $Column = $result->fetchcolumn (0);// Get the first field of the result set, such as 0 for the ID number, for the case of 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 ')//By what sort sequence, participates in array (multiple fields) or string (a field)->group ()//Group Having ()//packet query data conditions->DISTINCT ()//no parameters, remove duplicate values. Sometimes it is the same as the result returned by GroupBy->limit (10);//read result using binding parameter $params = Array (' Is_guo ' = ' 1 ', ' name ' = ' 12 ');//$sql = $ Select->__tostring ();//Get Query statements for debugging $result = $db->fetchall ($select, $params); query to execute select $stmt = $db->query ( $select); $result = $stmt->fetchall ();

or with

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

If you directly use

$db->fetchall ($select)

As a result

Multi-table Federated 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 ');//multi-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) Summary of data deletion

The first method: You can delete any of the tables

Quoteinto ($text, $value, $type = null, $count = null) $table = ' m_video ';//sets the table that needs to delete data $db = $this->getadapter (); $where = $db->quoteinto (' name =? ', ' CCC ');//The Where condition statement that deletes the data echo $rows _affected = $db->delete ($table, $where);//delete data and get shadow Number of rows ringing

The second method: You can only delete the

Delete usage//delete ($where) $where = "name = ' BBB '"; Echo $this->delete ($where);//The number of rows to delete data and get affected

(3) Summary of data Update

The first method: You can update any of the tables

Constructs the update array in the format "column name" and "Data", updates the data row $table = ' m_video ';//updated data table $db = $this->getadapter (); $set = Array (' name ' = ' "butterfly Shadow Heavy ', ' clicks ' = ' 888 ',); $where = $db->quoteinto (' id =? ', ' ten ');//Where statement//Update table data, returns the number of updated rows echo $rows _affected = $db-&G T;update ($table, $set, $where);

The second method: You can only update the

$set = Array (' name ' = ' butterfly shadow ', ' clicks ' = ' 8880 ',); $db = $this->getadapter (); $where = $db->quoteinto (' id = ? ', ');//WHERE statement $rows_affected = $this->update ($set, $where);//Update table data, return the updated number of rows

(4) Data Insertion Summary

The first method: You can insert data into any table

$table = ' M_gao ';//data table inserting data $db = $this->getadapter ();//construct insert array in format format "column name" = "Data", insert data row $row = Array (' title '   =& Gt ' Hello, everyone. 111 ', ' content ' = ' TV network to be changed to use Zend Framework development AH ', ' time ' = ' 2009-05-04 17:23:36 ', ';//insert a data row and return the number of rows inserted $rows_affected = $ Db->insert ($table, $row);//Last inserted data Idecho $last _insert_id = $db->lastinsertid (); $row =array (' name ' = = ') Curdate () ', ' address ' = new zend_db_expr (' Curdate () ')

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

The second method: only suitable for this table has not been summed up

(5) Transaction processing

$table = ' M_gao ';//data table for inserting data $db = $this->getadapter (); $db->begintransaction ();//zend_db_adapter will return to auto commit mode , until you call the BeginTransaction () method again//in the format of "column name" and "data" to construct the insert array, insert the data row $row = array (' ID ' =>null, ' title ' = '   everyone good. 111 ', ' content ' = ' TV network to be changed to use the Zend Framework development AH ', ' time ' = ' 2009-05-04 17:23:36 ', '; try {//Insert a data row 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 ' catch exception: '. $e->getmessage ();//Play Abnormal 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 of a table