Joining Tables With Zend Framework PHP

Question

Hi,

I am relatively new to the Zend Framework.

I understand the usage of Zend_Table and can obtain data using the Zend functions from the table associated with that class.

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

Im a little stumped how to active a select like the following 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)

BTW: It looks like you have 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 video id stored in 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());

Output:

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