Zend Framework database operation method example summary

The example in this article describes 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);
?>

Querying 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 to change the table name and column name that need to be queried. specified in this method. Both table names and column names can be replaced by aliases, and the from() method can 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 using?

<?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 the 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 through the total number and offset Limitations

Zend_db_select can support limit statement restrictions at 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, it is not so simple to implement the limit function, because they themselves do not support the limit statement at all. MS-SQL has a top statement to implement it, but if Oracle wants to implement the limit function, the way to write the query statement is more special. Due to the way zend_db_select works intrinsically, we can rewrite the select statement to implement the limit functionality 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 restriction through the number of pages and total number

Zend_db_select also provides a limit function for page turning. If you want to find a specific "number of pages" of results from the results, 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
//
?>

I hope this article will be helpful to everyone’s PHP programming based on the Zend Framework framework.

For more Zend Framework database operation method examples and summary related articles, please pay attention to the PHP Chinese website!