Re: [fw-db] howto convert mysql SQL to fw SQL?

Hi all,

New to Zend Framework and having a time of it with database access.

Using mysql I have this:

SELECT links.link, links.name
FROM links, titles
WHERE links.id = titles.id
AND titles.title = 'Home'

It returns this:
+-----------------+-------------------------------+
| name | link |
+-----------------+-------------------------------+
| Miscellanea | title=Miscellanea |
| Programmers | title=Programmers |
| General | title=General |
+-----------------+-------------------------------+

The closest I've come so far using the framework is:

$select = $this->db->select()
->from('links', array('links.name', 'links.link'))
->join('titles', 'titles.id = links.id')
->where('titles.title = ?', 'Home');

Which returns this:
+-----------------+-------------------------------+----+-------+
| name | link | id | title |
+-----------------+-------------------------------+----+-------+
| Miscellanea | title=Miscellanea | 1 | Home |
| Programmers | title=Programmers | 1 | Home |
| General | title=General | 1 | Home |
+-----------------+-------------------------------+----+-------+

Looking at the logs shows this is the query submitted:

SELECT `links`.`name`, `links`.`link`, `titles`.*
FROM `links`
INNER JOIN `titles`
ON titles.id = links.id
WHERE (titles.title = 'Home')

which has added 'titles.*' to the select statement.

I guess I have three questions:

Mike Wright-4 wrote:
>
> 1) Is there a better way to translate the mysql query string into the
> framework's query string? Must a JOIN be used?
>

Your original query used a join, using the old SQL-89 syntax with commas in
the FROM clause and the condition in the WHERE clause. The JOIN syntax was
established in the SQL-92 standard, and is now supported by every major
brand of database. This syntax should give the same results with the same
performance as the SQL-89 style syntax.

Mike Wright-4 wrote:
>
> 2) How can I limit the result set to just the two columns desired (link
> and name)?
>

Use an empty array() to specify that you desire no columns from the joined
table to be included in the select-list.

$select = $this->db->select()
->from('links', array('links.name', 'links.link'))
->join('titles', 'titles.id = links.id', array()) // empty
array for columns of `titles`
->where('titles.title = ?', 'Home');

Produces SQL:

SELECT `links`.`name`, `links`.`link`
FROM `links`
INNER JOIN `titles` ON titles.id = links.id
WHERE (titles.title = 'Home')

Mike Wright-4 wrote:
>
> 3) Anybody know of some tutorials about Zend_Db that cover multiple
> table selects?
>

I'll let other folks answer this. I think the manual for Zend_Db_Select
covers the join() method clearly.

Regards,
Bill Karwin
--
View this message in context: http://www.nabble.com/howto-convert-mysql-SQL-to-fw-SQL--tp19297095p19297389.html
Sent from the Zend DB mailing list archive at Nabble.com.