howto convert mysql SQL to fw SQL?

[Mike Wright]

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:

1) Is there a better way to translate the mysql query string into the
framework's query string?  Must a JOIN be used?

2) How can I limit the result set to just the two columns desired (link
and name)?

3) Anybody know of some tutorials about Zend_Db that cover multiple
table selects?

Thanks in advance to any helpers,
Mike Wright :m)

[BillKarwin]

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