Zend_db database knowledge (Zend framework Database Operations)

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 (); // you can obtain the recommended data of two homepage items.
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 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)
// Enclose the array with quotation marks 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
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); // 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) DataDeleteSummary
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) dataUpdateSummary
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' => '123 ',
);
$ 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' => '123 ',
);
$ 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) dataInsertSummary
Method 1: you can insert data into any table.
$ Table ='m _ gao'; // data table to be inserted
$ Db = $ this-> getadapter ();
// Insert an 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.
// Insert an 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