Join tables with Zend Framework PHP

Question

I am relatively new to the Zend Framework.

I understand the use of Zend_Table and can get data using Zend functions from the table associated with this class.

For example, I have a video table, and in another table I have a connection between the video and what category it is in.

It’s a little exaggerated how to activate the selection within the framework:

SELECT * FROM video , category WHERE category . category_id = 3 AND video . id = category . video_id

Any advice would be great.

Thanks.

Answer 1

 $db->select()->from('video')->joinInner('category','video.id = category.video_id')->where('category.category_id = ?',3) 

By the way: it looks like you have the wrong db design. You should have category_id in your video table (if 1 video → 1 category) or have a connection table (M: N), but it seems wrong to have the video ID saved in the category.

Answer 2

I would use Zend_Db_Select :

  $select = $this->db->select()->from(array('v' => 'video')) ->join(array('c' => 'category'),'v.id = c.video_id') ->where('c.category_id = ?', 3); print_r($select->__toString()); 

Conclusion:

 SELECT `v`.*, `c`.* FROM `video` AS `v` INNER JOIN `category` AS `c` ON v.id = c.video_id WHERE (c.category_id = 3)