Summary of zendframework database operations (DB operations)

(1) The data query summary fetchRow () method returns a row. whether to return the relevant array or use setFetchMode () to determine whether fetchCol () to return the first fetchOne () to return the first row, the first column. Returns the relevant array for a value not an array fetchAssoc (), which is equivalent to fetchAll (... "> <LINKhref =" http: // www

 

(1) Data Query summary

The fetchRow () method returns a row. Whether the returned result is an array or is determined by setFetchMode ()?
FetchCol () returns the first column
FetchOne () returns the first row and the first column. A value is not an array.
FetchAssoc () returns the relevant array, which is equivalent to the return value of fetchAll () by default.
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",)-> 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); // The default value is FETCH_NUM and 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, 0 is the ID number and is used to retrieve 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') // sort columns by which values are represented as arrays (multiple fields) or strings (one field)
-> Group () // group
-> Having () // conditions for querying data by group
-> Distinct () // no parameter. duplicate values are removed. Sometimes it is the same as the result returned by groupby.
-> Limit (10 );
// Use the bound parameter to read the result
$ Params = array ('is _ Guo' => '1', 'name' => 'Zodiac Romance ');
// $ SQL = $ select->__ toString (); // Obtain 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
$ Db-> fetchAll ($ select) results are the same
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'; // you can specify the table to be deleted.
$ 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 affected number of 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 and update data rows in the format of "column name" => "data"
$ Table ='m _ video'; // Updated data table
$ Db = $ this-> getAdapter ();
$ Set = array (
'Name' => 'Butterfly shadow ',
'Clicks' => '000000 ′,
);
$ 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 );

Method 2: only
$ Set = array (
'Name' => 'Butterfly shadow 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 to be inserted
$ Db = $ this-> getAdapter ();
// Construct an insert array and insert data rows in the format of "column name" => "data"
$ Row = array (
'Title' => 'Hello, everyone. 111 ′,
'Content' => 'change the video network to zend framework for Development ',
'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 id
Echo $ 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 to be 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 an insert array and insert data rows in the format of "column name" => "data"
$ Row = array (
'Id' => null,
'Title' => 'Hello, everyone. 111 ′,
'Content' => 'change the video network to zend framework for Development ',
'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 (); // output exception information
}
Echo $ last_insert_id;
(5) Others
$ Db = $ this-> getAdapter ();
$ Tables = $ db-> listTables (); // list all tables in the current database
$ Fields = $ db-> describeTable ('m _ video'); // list fields in a table