Zend Framework database operation method example summary

The examples in this article describe the Zend Framework database operation method. Share it with everyone for your reference, as follows:

Example:

<?php 
// 
// SELECT * 
//   FROM round_table 
//   WHERE noble_title = "Sir" 
//   ORDER BY first_name 
//   LIMIT 10 OFFSET 20 
// 
// 你可以使用一种重复定义的方式... 
$select->from(&#39;round_table&#39;, &#39;*&#39;); 
$select->where(&#39;noble_title = ?&#39;, &#39;Sir&#39;); 
$select->order(&#39;first_name&#39;); 
$select->limit(10,20); 
// ...或者使用一种连续定义的方式: 
$select->from(&#39;round_table&#39;, &#39;*&#39;) 
->where(&#39;noble_title = ?&#39;, &#39;Sir&#39;) 
->order(&#39;first_name&#39;) 
->limit(10,20); 
// 但是，读取数据的方法相同 
$sql = $select->__toString(); 
$result = $db->fetchAll($sql); 
// 对于以上任一种方式，你都可以传送$select对象本身 
// 使用Zend_Db_Select对象的 __toString()方法就可以得到查询语句 
$result = $db->fetchAll($select); 
?>

You can also use bound parameters in your query statement without adding quotes for the parameters yourself.

<?php 
// 
// SELECT * 
//   FROM round_table 
//   WHERE noble_title = "Sir" 
//   ORDER BY first_name 
//   LIMIT 10 OFFSET 20 
// 
$select->from(&#39;round_table&#39;, &#39;*&#39;)   
->where(&#39;noble_title = :title&#39;)   
->order(&#39;first_name&#39;)   
->limit(10,20); 
// 读取结果使用绑定的参数 
$params = array(&#39;title&#39; => &#39;Sir&#39;); 
$result = $db->fetchAll($select, $params); 
?>

Query multiple columns of data in the same table

When you need to query certain columns from a specified table, you can use the from() method, and the table name and column name to be queried are specified in this method. Both table names and column names can be replaced by aliases, and the from() method can also be used as many times as needed.

<?php 
// 创建一个$db对象，假定adapter为Mysql 
$select = $db->select(); 
// 从some_table表中读取a,b,c三列 
$select->from(&#39;some_table&#39;, &#39;a, b, c&#39;); 
// 同样可以: 
$select->from(&#39;some_table&#39;, array(&#39;a&#39;, &#39;b&#39;, &#39;c&#39;); 
// 从foo AS bar表中读取列bar.col 
$select->from(&#39;foo AS bar&#39;, &#39;bar.col&#39;); 
// 从foo, bar两个表中读取foo.col 别名为col1，bar.col别名为col2 
$select->from(&#39;foo&#39;, &#39;foo.col AS col1&#39;); 
$select->from(&#39;bar&#39;, &#39;bar.col AS col2&#39;); 
?>

Multi-table joint query

When you need to perform a table joint query, you can use the join() method. First, set the table name for table union query, then the conditions for table union (ares note: this condition is for internal joins of multiple tables), and finally the column name of the query. Likewise, you can use the join() method as many times as needed.

<?php 
// 创建一个$db对象，假定adapter为Mysql. 
$select = $db->select(); 
// 
// SELECT foo.*, bar.* 
//   FROM foo 
//   JOIN bar ON foo.id = bar.id 
// 
$select->from(&#39;foo&#39;, &#39;*&#39;); 
$select->join(&#39;bar&#39;, &#39;foo.id = bar.id&#39;, &#39;*&#39;); 
?>

WHERE condition

When you need to add a where condition, you can use the where() method. You can pass a normal query string, or you can pass a use?

<?php 
// 创建一个$db对象，调用SELECT方法. 
$select = $db->select(); 
// 
// SELECT * 
//   FROM round_table 
//   WHERE noble_title = "Sir" 
//   AND favorite_color = "yellow" 
// 
select->from(&#39;round_table&#39;, &#39;*&#39;); 
$select->where(&#39;noble_title = "Sir"&#39;); // embedded value 
$select->where(&#39;favorite_color = ?&#39;, &#39;yellow&#39;); // quoted value 
// 
// SELECT * 
//   FROM foo 
//   WHERE bar = "baz" 
//   OR id IN("1", "2", "3") 
// 
$select->from(&#39;foo&#39;, &#39;*&#39;); 
$select->where(&#39;bar = ?&#39;, &#39;baz&#39;); 
$select->orWhere(&#39;id IN(?)&#39;, array(1, 2, 3); 
?>

GROUP BY clause

As needed, you can use the group() method multiple times to group the queried data

<?php 
// 创建一个$db对象，调用SELECT方法. 
$select = $db->select(); 
// 
// SELECT COUNT(id) 
//   FROM foo 
//   GROUP BY bar, baz 
// 
$select->from(&#39;foo&#39;, &#39;COUNT(id)&#39;); 
$select->group(&#39;bar&#39;); 
$select->group(&#39;baz&#39;); 
// 同样可以这样调用 group() 方法: 
$select->group(&#39;bar, baz&#39;); 
// 还可以: 
$select->group(array(&#39;bar&#39;, &#39;baz&#39;)); 
?>

HAVING conditions

When you need to add having conditions to the query results, you can use having() method. This method has the same function as the where() method.

When you call the having() method multiple times, the conditions of each having will be "merged" together for operation; if you need to implement the "or" operation, you can use the orHaving() method .

<?php 
// 创建一个$db对象，调用SELECT方法. 
$select = $db->select(); 
// 
// SELECT COUNT(id) AS count_id 
//   FROM foo 
//   GROUP BY bar, baz 
//   HAVING count_id > "1" 
// 
$select->from(&#39;foo&#39;, &#39;COUNT(id) AS count_id&#39;); 
$select->group(&#39;bar, baz&#39;); 
$select->having(&#39;count_id > ?&#39;, 1); 
?>

ORDER BY clause

As needed, you can use the order() method multiple times to sort the queried data

<?php 
// 创建一个$db对象，调用SELECT方法. 
$select = $db->select(); 
// 
// SELECT * FROM round_table 
//   ORDER BY noble_title DESC, first_name ASC 
// 
$select->from(&#39;round_table&#39;, &#39;*&#39;); 
$select->order(&#39;noble_title DESC&#39;); 
$select->order(&#39;first_name&#39;); 
// 同样可以这样调用 order() 方法: 
$select->order(&#39;noble_title DESC, first_name&#39;); 
// 还可以: 
$select->order(array(&#39;noble_title DESC&#39;, &#39;first_name&#39;)); 
?>

LIMIT limit through the total number and offset

Zend_db_select can support the limit statement limit of the database layer . For some databases, such as mysql and postgresql, implementing this is relatively easy because these databases natively support the "limit:count" syntax.

For some other databases, such as Microsoft's sqlserver and oracle, the limit function must be implemented It's not that simple, because they don't support the limit statement at all. MS-SQL has a slang To achieve this, if Oracle wants to implement the limit function, the query statement must be written in a more special way. because The way zend_db_select works intrinsically, we can rewrite the select statement to implement the limit function of the above open source database system in Oracle.

To limit the returned results by setting the total number and offset of the query, you can use the limit() method, with the total value and an optional offset as parameters for calling this method.

<?php 
// 首先，一个简单的 "LIMIT :count" 
$select = $db->select(); 
$select->from(&#39;foo&#39;, &#39;*&#39;); 
$select->order(&#39;id&#39;); 
$select->limit(10); 
// 
// 在mysql/psotgreSql/SQLite,可以得到这样的语句： 
// 
// SELECT * FROM foo 
//   ORDER BY id ASC 
//   LIMIT 10 
// 
// 但是在Microsoft SQL下,可以得到这样的语句： 
// 
// SELECT TOP 10 * FROM FOO 
//   ORDER BY id ASC 
// 
// 
// 现在, 是更复杂的 "LIMIT :count OFFSET :offset"方法 
$select = $db->select(); 
$select->from(&#39;foo&#39;, &#39;*&#39;); 
$select->order(&#39;id&#39;); 
$select->limit(10, 20); 
// 
// 在mysql/psotgreSql/SQLite,可以得到这样的语句： 
// 
// SELECT * FROM foo 
//   ORDER BY id ASC 
//   LIMIT 10 OFFSET 20 
// 
// 但是在Microsoft SQL下,由于不支持偏移量功能,可以得到这样sql语句: 
// 
// SELECT * FROM ( 
//   SELECT TOP 10 * FROM ( 
//     SELECT TOP 30 * FROM foo ORDER BY id DESC 
//   ) ORDER BY id ASC 
// ) 
// 
// Zend_Db_Adapter 可以自动的完成sql语句的动态创建. 
// 
?>

Limit limit by page number and total number

Zend_db_select also provides page turning limit function. If you want to find from the results For results of a specific "number of pages", use the limitPage() method; just pass the page value you need and the number of data values ​​displayed on each page as parameters.

<?php 
// 构造基础的select方法: 
$select = $db->select(); 
$select->from(&#39;foo&#39;, &#39;*&#39;); 
$select->order(&#39;id&#39;); 
// ... 限制到第三页,每页包括10行数据 
$select->limitPage(3, 10); 
// 
// 在MySQL/PostgreSQL/SQLite下, 可以得到: 
// 
// SELECT * FROM foo 
//   ORDER BY id ASC 
//   LIMIT 10 OFFSET 20 
// 
?>