Zend Framework database summary [original]

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 (Chinese zodiac, 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) // What sort columns are used 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 => 12 Chinese 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 shadows,
Clicks = & gt; 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 );

Method 2: only
$ Set = array (
Name => 22,
Clicks = & gt; 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) data insertion Summary
Method 1: you can insert data into any table.
$ Table = m_gao; // data table for data insertion
$ Db = $ this-> getAdapter ();
// Insert an array and insert data rows in the format of "column name" => "data"
$ Row = array (
Title => hello, everyone. 111,
Content => I want to use zend framework for the development of the film and TV network,
Time => 17:23:36,
);
// Insert data rows and return the number of inserted rows
$ Rows_affected = $ db-> insert ($ table, $