Re: [fw-db] how to use zend db with joins

Jeroen, thanks, yes this helps a lot. when i use raw queries for update can I then just assume that the database connection is still there (I read something about lazy connections) or do I have to approach the database with regular mysqli? Is it good practice to mix zend db code with raw php/mysqli for the same model/database?

Timmo

On 23 June 2010 11:12, Jeroen Keppens <jeroen.keppens@gmail.com> wrote:

I would say that Zend_Db_Select is the way to go to do your joins in selects:

Example from the docs:

1. // Build this query:
2. //   SELECT p."product_id", p."product_name"
3. //   FROM "products" AS p JOIN "line_items" AS l
4. //     ON p.product_id = l.product_id
5.  
6. $select = $db->select()
7.              ->from(array('p' => 'products'),
8.                     array('product_id', 'product_name'))
9.              ->join(array('l' => 'line_items'),
10.                     'p.product_id = l.product_id',
11.                     array() ); // empty list of columns

If you want fields from the second table add the column names in the example where it says "empty list of columns".

If you want to do updates on multiple tables using joins, you'll have to use raw queries since ZF db->update does not support that as far as I know.

Hope this helps!

Wkr

Jeroen

http://blog.amazium.com

On 23 Jun 2010, at 10:58, zensys wrote:

Just a general question on how to best utilise zend db. I have a few tables
which I want to display with join statements and update afterwards. Not a
very complicated schema but quite some fields which I want to select
individually. To my limited understanding of DB I have the following
options:

- instantiate an adapter and just go from there using db->query() etc.
- use db select
- instantiate a db table for each of my tables and use joins (or db select?)
from there.

From what I've read in the manual it seems like only use an adapter gives

the least overhead. Db select is only for complicated queries and anyway you
cannot update with joins. Instantiate db tables for each table also sounds
like some overhead and I do not see the advantages.

Anyone any thoughts on this?

And another thing: do I need to instantiate an adapter but have the
resource.db.params in my application.ini . In that case, is the adapter
already instantiated and if so how do I reference it. If not, using the
db_factory? But it asks for parameters.
--
View this message in context: http://zend-framework-community.634137.n4.nabble.com/how-to-use-zend-db-with-joins-tp2265294p2265294.html
Sent from the Zend DB mailing list archive at Nabble.com.