ZendFramework database operation tips

This article mainly introduces the ZendFramework database operation skills, and summarizes and analyzes the common functions, common operations, and precautions of ZendFramework for database operations based on the instance form, for more information about Zend Framework database operations, see the next article. This article summarizes and analyzes the common functions, common operations, and precautions of Zend Framework for database operations in combination with instances, for more information, see

This example summarizes the Zend Framework database operations. We will share this with you for your reference. The details are as follows:

Zend_Db database knowledge

Example:

Model File:

$ This-> fetchAll ("is_jian = 1", "id DESC",)-> toArray (); // according to is_jian = 1, sort by id in reverse order. when the first two records are null, the ASC is directly sorted by id in reverse order.

Route file:

$ Video = new Video (); // instantiate the database class $ this-> view-> get2Video = $ video-> get2Video (); // obtain the data recommended on the home page

Index. phtml file:

 get2Video as $video): ?>
 
 
 

Add quotation marks to prevent database attacks

Quote usage

$ Value = $ db-> quote ('St John "s wort '); // $ value is now '"St John \" s Wort "' (note the quotation marks on both sides) // enclose the array with quotation marks $ value = $ db-> quote (array ('A', 'B', 'C ')); // $ value is now '"a", "B", "c"' ("," separator string)

QuoteInto usage

Echo $ where = $ db-> quoteInto ('Id =? ', 1); // $ where is now 'Id = "1"' (note the quotation marks on both sides) // IN The where statement, enclose the array with quotation marks $ 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

Directly query (using the 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 the result data into an array $ videoArray = $ result-> fetchAll ();

FetchAll usage

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

Return the values of all fields in the result set as a continuous array. if the parameter is not set, it is written as null.

Number of results that 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 the associated 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())

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

Retrieves an array. The first field value is a code (id), and the second field is a value (name)

Return value: Array ([1] => Chinese Zodiac [2] => peach blossom luck), 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 ('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 ); // obtain the first field of the result set. for example, if 0 is the ID number, print_r ($ rows) is used to retrieve only one field );

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 columns by which columns are added as arrays (multiple fields) or strings (one field)-> group () // group-> having () // the condition for querying data by group-> distinct () // no parameter is set to remove duplicate values. Sometimes it is the same as the result returned by groupby-> limit (10); // use the bound parameter $ params = array ('is _ Guo' => '1 ', 'name' => 'Zodiac Romance '); // $ SQL = $ select->__ toString (); // Obtain the query statement, debug $ 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

$db->fetchAll($select)

Same Results

Multi-table join 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 );

The 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

Method 1: You can delete any table.

// QuoteInto ($ text, $ value, $ type = null, $ count = null) $ table ='m _ video '; // set the table for data deletion $ db = $ this-> getAdapter (); $ where = $ db-> quoteInto ('name =? ', 'CCC'); // where condition statement for data deletion echo $ rows_affected = $ db-> delete ($ table, $ where ); // delete the data and obtain the number of affected rows

Method 2: only

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

(3) Data Update Summary

Method 1: Any table can be updated.

// Construct an update array in the format of "column name" => "data" and update the data row $ table ='m _ video '; // update the data table $ db = $ this-> getAdapter (); $ set = array ('name' => 'Butterfly shadow ', 'clicks' => '20140901',); $ where = $ db-> quoteInto ('Id =? ', '10'); // where statement // update table data, returns the number of updated rows echo $ rows_affected = $ db-> update ($ table, $ set, $ where );

Method 2: only

$ Set = array ('name' => ' 22', 'clicks' => '000000',); $ 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) Data insertion summary

Method 1: You can insert data into any table.

$ Table ='m _ Gao'; // data table inserted $ 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' =>' video and TV network should be developed with zend framework ', 'Time' => '2017-05-04 17:23:36 ',); // insert data rows and return the number of inserted rows $ rows_affected = $ db-> insert ($ table, $ row ); // The 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 string of curdate (), and the address inserts a time value (curdate () result)

Method 2: It can only be applicable to the table that has not been summarized.

(5) transaction processing

$ Table ='m _ Gao'; // data table inserted $ db = $ this-> getAdapter (); $ db-> beginTransaction (); // The Zend_Db_Adapter will return to the automatic commit mode until you call the beginTransaction () method again. // Construct the inserted array in the format of "column name" => "data, insert data row $ row = array ('id' => null, 'title' => 'Hello, everyone. 111 ', 'content' =>' video and TV network should be developed with zend framework ', 'Time' => '2017-05-04 17:23:36 ',); try {// insert data rows and return the number of inserted rows $ rows_affected = $ db-> insert ($ table, $ row ); // The Last inserted data id $ last_insert_id = $ db-> lastInsertId (); $ db-> commit (); // transaction commit} catch (Exception $ e) {$ db-> rollBack (); echo 'capture exception :'. $ e-> getMessage (); // exception message} echo $ last_insert_id;

(6) Others

$ Db = $ this-> getAdapter (); $ tables = $ db-> listTables (); // list all tables in the current database $ fields = $ db-> describeTable ('m _ video'); // list the fields of a table

For more information about Zend Framework database operation tips, see PHP!