Zend Framework Database Operation Method Example Summary _php example

This article describes the Zend Framework database operation method. Share to everyone for your reference, specific as follows:

Example:

<?php
///
SELECT *
//from   round_table
//   WHERE noble_title = ' Sir '
//   ORDER BY First_Name
//   LIMIT
(
//////////////////////////////) $select->from (' round_table ', ' * ');
$select->where (' Noble_title =? ', ' Sir ');
$select->order (' first_name ');
$select->limit (10,20);
// ... Or use a sequential definition:
$select->from (' round_table ', ' * ')
->where (' Noble_title =? ', ' Sir ')
->order (' first_name ')
->limit (10,20);
However, the method of reading the data is the same
$sql = $select->__tostring ();
$result = $db->fetchall ($sql);
In either of these ways, you can transfer the $select object itself
//__tostring () method using the Zend_db_select object to get the query statement
$result = $db->fetchall ( $select);
? >

You can also use the binding parameters in your query, without having to quote the parameters yourself.

<?php
///
SELECT *
//from   round_table
//   WHERE noble_title = ' Sir '
//   ORDER BY First_Name
//   LIMIT OFFSET
//
$select->from (' round_table ', ' * ')
  ->where (' noble _title =: Title ')
  ->order (' first_name ')
  ->limit (10,20);
The read result uses the binding parameter
$params = Array (' title ' => ' Sir ');
$result = $db->fetchall ($select, $params);
>

Querying multiple columns of data in the same table

When you need to query a certain column from a specified table, you can use the from () method to specify the table and column names that you want to query in the method. Table and column names can be replaced by aliases, and you can use the From () method as many times as you want.

<?php
//Create a $db object, assuming that adapter is a mysql
$select = $db->select ();
Read A,b,c three columns from the some_table table
$select->from (' some_table ', ' A, B, C ');
The same can be:
$select->from (' some_table ', Array (' A ', ' B ', ' C ');
Reads the column Bar.col
$select->from (' foo as Bar ', ' Bar.col ') from the Foo as bar table;
Read the Foo.col alias from Foo, bar two tables as Col1,bar.col alias col2
$select->from (' foo ', ' Foo.col as Col1 ');
$select->from (' Bar ', ' bar.col as Col2 ');
? >

Multi-table Joint query

You can use the join () method when you want to make a table union query. First, you set the table name for the table union query, then the criteria for the table Union (Ares Note: The condition is the condition for the inner join of multiple tables), and finally the column name of the query. Again, you can use the join () method as many times as you need to.

<?php
//Create a $db object, assuming adapter is MySQL.
$select = $db->select ();
//SELECT foo.*, bar.*
//from   foo
//   JOIN bar on foo.id = bar.id
//
$select-> From (' foo ', ' * ');
$select->join (' Bar ', ' foo.id = Bar.id ', ' * ');
? >

Where condition

You can use the where () method when you want to increase the where condition. Can you send an ordinary query statement string, or can you send a use?

<?php
//Creates a $db object and invokes the Select method.
$select = $db->select ();
//SELECT *
/from   round_table
//   WHERE noble_title = "Sir"
//and   Favorite_Color = " Yellow "
//
$select->from (' round_table ', ' * ');
$select->where (' noble_title = ' Sir '); Embedded value
$select->where (' Favorite_Color =? ', ' yellow ');//Quoted value
///
SELECT *   from foo
//   WHERE bar = "Baz"
//   or IDs in ("1", "2", "3")
//
$select->from (' Foo ', ' * ');
$select->where (' bar =? ', ' Baz ');
$select->orwhere (' ID in (?) ', Array (1, 2, 3);
? >

GROUP BY clause

You can use the group () method multiple times to group the data that you query, as needed

<?php
//Creates a $db object and invokes the Select method.
$select = $db->select ();
//SELECT COUNT (ID)
//   from Foo
//   GROUP by Bar, Baz
//
$select->from (' foo ', ' COUNT (ID) ");
$select->group (' Bar ');
$select->group (' Baz ');
You can also call the group () method like this:
$select->group (' Bar, Baz ');
can also:
$select->group (' Bar ', ' Baz '));
? >

Having conditions

You can use the having () method when you want to include a having condition in the query results. This method is the same as the function of the Where () method.

When you call the having () method more than once, each having condition will "and" work together, and if you need to implement the "or" action, you can use the Orhaving () method.

<?php
//Creates a $db object and invokes the Select method.
$select = $db->select ();
//SELECT COUNT (ID) as count_id
//   from Foo
//   GROUP by Bar, Baz
//have   Count_ ID > "1"
//
$select->from (' foo ', ' COUNT (ID) as count_id ');
$select->group (' Bar, Baz ');
$select->having (' count_id > ', 1);
? >

ORDER BY clause

The order () method can be used more than once to sort the data that is queried, as needed

<?php
//Creates a $db object and invokes the Select method.
$select = $db->select ();
//SELECT * from round_table
//   ORDER by Noble_title DESC, first_name ASC
//
$select->from (' Round_table ', ' * ');
$select->order (' Noble_title DESC ');
$select->order (' first_name ');
The order () method can also be invoked like this:
$select->order (' Noble_title DESC, first_name ');
Also:
$select->order (Array (' Noble_title DESC ', ' first_name '));
? >

Limit limits by total and offset

Zend_db_select can support limit statement limits at the database level. For some databases, such as MySQL and PostgreSQL, it is relatively easy to implement these, because the databases themselves support the "limit:count" syntax.

For other databases, such as Microsoft SQL Server and Oracle, it is not easy to implement the limit feature because they do not support limit statements at all. Ms-sql has a top statement to implement, and Oracle to implement the limit function, the query statement is more special. Because of the way in which Zend_db_select works internally, we can rewrite the SELECT statement to implement the limit functionality of the above Open-source database system in Oracle.

To limit the results returned by setting the total and offset of the query, you can use the limit () method, the total value, and an optional offset as the parameter that invokes the method.

<?php
//First, a simple "limit:count"
$select = $db->select ();
$select->from (' foo ', ' * ');
$select->order (' id ');
$select->limit (a);
//In Mysql/psotgresql/sqlite, you can get this statement:////
SELECT * from foo
//ORDER by   ID ASC
//   LIMIT
///
but under Microsoft SQL, you can get a statement like this:////
SELECT Top * out
FOO
//   Order By ID ASC/////
Now, is the more complex "Limit:count offset:offset" method
$select = $db->select ();
$select->from (' foo ', ' * ');
$select->order (' id ');
$select->limit (a);
//In Mysql/psotgresql/sqlite, you can get this statement:////
SELECT * from foo
//ORDER by   ID ASC
//< C34/>limit///
but under Microsoft SQL, this SQL statement can be obtained because the offset feature is not supported:////
SELECT * FROM (   Select Top * to (
//     Select top * from foo-ID DESC
//) Order by   ID ASC
/////
Zend_db_adapter can automatically complete the dynamic creation of SQL statements.
?>

Limit limits by number of pages and totals

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

<?php
//Structure based Select method:
$select = $db->select ();
$select->from (' foo ', ' * ');
$select->order (' id ');
// ... Limit to the third page, each page includes 10 rows of data
$select->limitpage (3);
//Under Mysql/postgresql/sqlite, you can get:////
SELECT * from foo
//ORDER by   ID ASC
//   LIMIT OFFSET
//
?>

More interested in Zend related content readers can view the site topics: "The introduction of the Zend Framework frame", "PHP Excellent Development Framework Summary", "Yii framework Introduction and common skills Summary", "thinkphp Introductory Course", "PHP object-oriented Programming Program , "Php+mysql Database operation Introduction Tutorial" and "PHP common database Operation Skills Summary"

I hope this article will help you with the PHP program design based on the Zend Framework.