Joining Tables With Zend Framework PHP

Question

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.

score 2 · Answer 1 · answered Jul 29 '09 at 11:23

2

$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.

answered Jul 29 '09 at 11:23

Tomáš Fejfar

11,129
8
54
82

just a short fyi. $sql = $this->getDbTable()->select()->where('group_id = ?', $group_id)->joinInner('category','video.id = category.video_id')->where('category.category_id = ?',3); $result = $this->getDbTable()->fetchRow($sql); – wenbert Jul 30 '09 at 11:27

score 1 · Answer 2 · answered Jul 29 '09 at 11:24

1

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)

answered Jul 29 '09 at 11:24

karim79

339,989
67
413
406

how would you read columns from the category table in this example. is it expected that the Zend_Db_Table_Abstract would define the referenceMap or can i pass an array of specific category column names to read? – emeraldjava Sep 16 '09 at 22:13

Joining Tables With Zend Framework PHP

2 Answers2

Linked