Zend_db database knowledge (Zend framework Database Operations)

Source: Internet
Author: User
Tags php foreach zend framework
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

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.