Zend Framework database summary [original]

Source: Internet
Author: User
Tags php foreach

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, $

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.