How to query multiple tables in Zend framework

Document directory

• Comments

Introduction to zend_db_select

Using the zend_db_select method is a tool for creating select SQL statements without database constraints (Ares Note: You can use this method to generate SQL statements for queries, instead of considering the differences between SQL statements in different databases ). Although this method is obviously not perfect, it does provide us with a method to help us perform the same query in different backend databases. In addition, it can avoid SQL statement attacks.

The simplest way to create a zend_db_select instance is to use the zend_db_adapter: Select () method.

<? Phprequire_once 'zend/DB. PHP '; $ Params = array ('host' =>' 127. 0.0.1 ', 'username' => 'malory', 'Password' => '******', 'dbname' => 'camelot '); $ db = zend_db: Factory ('pdo _ mysql', $ Params); $ select = $ db-> select (); // $ select is a zend_db_select_pdomysql object now?>

You can use this object and its corresponding method to construct a SELECT query statement, and then generate a string to send it to zend_db_adapter for query or reading the result.

<? PHP // select * // from round_table // Where noble_title = "sir" // order by first_name // limit 10 offset 20 // you can use a duplicate definition method... $ select-> from ('round _ table', '*'); $ select-> where ('noble _ Title =? ', 'Sir'); $ select-> Order ('first _ name'); $ select-> limit (10, 20 );//... or use a continuous definition method: $ select-> from ('round _ table', '*')-> where ('noble _ Title =? ', 'Sir')-> Order ('first _ name')-> limit (10, 20); // However, the method for reading data is the same $ SQL = $ select->__ tostring (); $ result = $ db-> fetchall ($ SQL); // for any of the preceding methods, you can transmit the $ select object itself // use the _ tostring () method of the zend_db_select object to obtain the query statement $ result = $ db-> fetchall ($ select);?>

You can also use the bound parameters in your query statement without quotation marks.

<? PHP // select * // from round_table // Where noble_title = "sir" // order by first_name // limit 10 offset 20 // $ select-> from ('round _ table ', '*')-> where ('noble _ Title =: title')-> Order ('first _ name')-> limit (10, 20 ); // use the bound parameter $ Params = array ('title' => 'Sir ') to read the result; $ result = $ db-> fetchall ($ select, $ Params);?>

Query multiple columns of data in the same table

When you want to query several columns from a specified table, you can use the from () method to specify the table name and column name to be queried in this method. Both the table name and column name can be replaced by an alias, And the from () method can be used multiple times as needed.

<? PHP // create a $ db object. Assume that the adapter is MySQL $ select = $ db-> select (); // read A, B, c columns $ select-> from ('some _ table', 'a, B, C'); // You can also: $ select-> from ('some _ table', array ('A', 'B', 'C'); // read the column bar from the foo as bar table. col $ select-> from ('foo as bar', 'bar. col '); // read Foo from the Foo and bar tables. col alias is col1, bar. col alias is 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 to perform table join queries. First, set the name of the table for table Union query, then the conditions for table Union (Ares Note: This condition is for multi-table internal join conditions), and finally the name of the queried column. Similarly, you can use the join () method multiple times as needed.

<? PHP // create a $ db object, assuming that the 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', '*');?>

So far, Zend framework only supports the syntax of combining common internal tables, but does not support the combination of left and right, and other external connection methods. In future versions, more connections will be supported.

Where condition

You can use the where () method to add a where condition. Can you transmit a common query statement string or use it? As a placeholder string, and then add the data processed by quotation marks to the placeholder (data will be processed using the zend_db_adapter: quoteinto method)

Multiple callto where () Will and the conditions together; if you need to or a condition, use orwhere ().

<? PHP // create a $ db object and call 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 ID 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 queried data as needed.

<? PHP // create a $ db object and call 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 call the group () method as follows: $ select-> group ('bar, Baz'); // You can also: $ select-> group (Array ('bar', 'baz');?>

Having Condition

You can use the having () method to add the having condition to the query results. This method is similar to the where () method ..

When you call the having () method multiple times, the having conditions are "and" operated together. If you need to implement the "or" operation, you can use the orhaving () method.

<? PHP // create a $ db object and call the select method. $ select = $ db-> select (); // select count (ID) as count_id // From Foo // group by bar, baz // having count_id> "1" // $ select-> from ('foo', 'count (ID) as count_id '); $ select-> group ('bar, baz'); $ select-> having ('count _ id>? ', 1);?>

Order by clause

You can use the order () method multiple times to sort the queried data as needed.

<? PHP // create a $ db object and call 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'); // you can call the order () method as follows: $ select-> Order ('noble _ title DESC, first_name '); // You can also: $ select-> Order (Array ('noble _ title DESC ', 'First _ name');?>

Limit limit by sum and offset

Zend_db_select supports limit statement restrictions at the database layer. It is relatively easy to implement some databases, such as MySQL and PostgreSQL, because these databases themselves support the "Limit: Count" syntax.

For some other databases, such as Microsoft sqlserver and Oracle, it is not easy to implement the limit function because they do not support limit statements. MS-SQL has a top sentence to achieve, and Oracle to implement the limit function, the query statement writing is more special. Because zend_db_select internally works, 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, the total number, and an optional offset as parameters to call this method.

<? PHP // first, a simple "Limit: Count" $ select = $ db-> select (); $ select-> from ('foo ','*'); $ select-> Order ('id'); $ select-> limit (10); // you can obtain the following statement in MySQL/psotgresql/SQLite: /// select * From Foo // order by id asc // limit 10 /// however, in Microsoft SQL, you can obtain the following statement: //// select top 10 * From Foo // order by id asc // now, it is more complex "Limit: Count offset: offset "method $ select = $ db-> select (); $ select-> from ('foo', '*'); $ select-> orde R ('id'); $ select-> limit (10, 20); // in MySQL/psotgresql/SQLite, you can get the following statement: /// select * From Foo // order by id asc // limit 10 offset 20 /// however, in Microsoft SQL, the offset function is not supported, you can obtain the following SQL statement: // select * from (// select top 10 * from (// select top 30 * From Foo order by ID DESC //) order by id asc //) /// zend_db_adapter can automatically create SQL statements dynamically. //?>

Limit the number of pages and total number of pages

Zend_db_select also provides the paging limit function. If you want to find the result of a specific "page number" from the result, use the limitpage () method; pass the required page value and the number of data values displayed on each page as parameters.

<? PHP // construct the basic select method: $ select = $ db-> select (); $ select-> from ('foo ','*'); $ select-> Order ('id ');//... limit to the third page. Each page contains 10 rows of Data $ select-> limitpage (3, 10); // in MySQL/PostgreSQL/SQLite, you can get: /// select * From Foo // order by id asc // limit 10 offset 20 //?>

Zend_db_profiler

Zend_db Zend framework Chinese Version

Zend_db_table

Commentsposted by: Shah mubashir on: 2010-03-08 06: 51: 59hi ZF developer,

I am new to Zend frame work and I am trying to develop a site using Zend framework. I am having problem with the fetching of records at first hand. meaning there are lots of problem which I am facing developing a site. my first problem is of fetching the data.

In my index page the records which I am trying to fetch will come from two tables. so there the $ model-> fetchall () is not helpful to me. I want to use the zend_db_select to fetch the records from two tables. as it is said in the examples it can be achived by zend_db_select.

My question is in the model class which is extended from zend_db_table_abstract contains a zend_db_select object or do I have to create it explicitly to execute my queries. if I have to explicitly make an object of zend_db_select so how am I going to do that.

I am waiting for your answer. Please do reply

Regards,
Shah mubashir Hussain.

Posted by: Raul sandrea on: 2010-03-16 21: 20: 41 greetings Shah,

To successfully make a query on 2 tables you need to create a zend_db_select object (zend_db_table works as an interface to single table operations ).

The usage it's fairly simple, you create an adapter (you can check for the available adapters on the folder Zend/DB/adapter of your ZF installation ), then from that adapter use the method-> select () to create your select object, then add the tables names/schemas as needed.

A quick example cocould be:
$ Dbconn = new zend_db_adapter_pdo_pgsql ($ conn); // a PostgreSQL connection where $ conn contains the host, username, password, etc for the connection

$ Select = $ dbconn-> select ();
$ Select-> from ('table1 ',
Array ('column1 ', 'column2 ')
)
-> From ('table2 ',
Array ('column1 ', 'column2 ')
)
-> Where ('condition ') // if needed
....

I hope this helps.

Regards,
Raul sandrea

Posted by: Shah mubashir on: 2010-03-18 10: 07: 27 thanks Raul sandrea for your comment.

I will definitely try it out.

Correct me if I am wrong. What u are suggesting is that I shoshould replace my model class (application_model_dbtable_xxx extends zend_db_table_abstract)
Yours (application_model_dbtable_xxx extends zend_db_adapter_pdo_pgsql ).

Right.

And then I shoshould create a select object and start using it.

Thanks again Raul sandrea.

Posted by: Shah mubashir Hussain on: 2010-03-21 17: 18: 33 then thanks Raul sandrea, it worked.

This is how my class looks like now.

Class application_model_dbtable_albums extends zend_db_adapter_mysqli
{
Protected $ _ name = 'albums ';
Private Static $ database;
Private const $ _ option = array (
'Host' => 'localhost ',
'Username' => 'root ',
'Password' => '',
'Dbname' => 'xxx ');

Public Function _ construct ()
{
Parent: :__ construct ($ this-> _ option );
$ This-> database = $ this-> select ();
}
.
.
.
}

Posted by: Raul sandrea on: 2010-04-05 20: 01: 43 gglad I cocould help =) posted by: Jeffery Fernandez on: 2010-04-13 23: 35: 39i don't understand why there is no mention of setintegritycheck (false) when you are making a join statement. I spent hours trying to figure out why my join query was giving error of "SELECT query cannot join with another table ".

Maybe the notes in the zend_db_table chapter shoshould be added to this one as it is relevant to join statements.

Posted by: Ernest szulikoski on: 2010-06-06 11:19:50 Please note: [\ B]

The code similar to one in example #20 can lead to SQL injection! See http://www.zend.com/webinar/Framework/70170000000bEs9-webinar-secure-application-development-with-the-ZF-20100505.flv for more.

Posted by: everaldo Ribeiro on: 2010-09-01 11: 58: 13for to get the maximum ID inside a model this function runs perfectly.

/* This function returns the last insert ID */
Public Function lastinsertid (){
$ ROW = $ this-> createrow ();
$ Id = $ this-> _ DB-> lastinsertid ();
Return $ ID;
}

Everaldo Ribeiro, cientec-everaldoribeiro01@gmail.com

Posted by: Alexander R on: 2010-09-07 10: 17: 22i wrote a class that transform an existing MySQL query to zend_db_table_select statement.
Something like:

string(106) "SELECT * FROM table1 WHERE a = 'test' AND (b='test2' OR c='test3' AND (d = 'test4 )) AND 1=1"
 | |
 \ /
string(144) "$this->select()
    ->from('table1', '*')
    ->where("a = 'test'")
    ->where("b='test2' OR c='test3' AND (d = 'test4 )")
    ->where("1=1")"

Code
Maybe someone will suit the same lazy as I am.