CODEWITHSUNDEEP
phpmysqlselectzend-frameworkfetch
Peter
Peter

Reputation: 215

Zend Framework 1. Fetch comments

I can't create an appropriate query which could select all comments connected with one particular image and get those comments authors. I would like to create a query something like:

select a comment where comment_id == image_id && user_id(table comments) == user_id(table users)

This is MySQL part:

Table 'comments'
id | comment | user_id | image_id |
1  | test 1  |    1    |     1    |
2  | test 2  |    1    |     2    |
3  | test 3  |    2    |     1    |


Table 'users'
id |  name  |
1  |  test1 |
2  |  test2 |


Table 'images'
id |    img     |
1  |  test.jpg  |
2  |  test.jpg  |
3  |  test.jpg  |
4  |  test.jpg  |

Controller Part:

$imageId = $filter->filter ($request->getParam('id'));
$this->view->imageId = $filter->filter ($request->getParam('id'));
$this->view->imageChosen = $images->fetchRow($images->select()->where('id = ?', $imageId));

$users = new Users();
$userChosen = new Users();
$comments = new Comments();
$this->view->comments = $comments->fetchAll();

$this->view->userChosen = $users->fetchRow($users->select()->where('id = ?', $this->view->imageChosen->author_id));
$this->view->commentsChosen = $comments->fetchAll($comments->select()->where('id = ?', $imageId));

View part:

for ($i=0; $i < count($this->commentsChosen); $i++) {
    echo $this->commentChosen[$i]->comment;
}

Right now I only get the very first comment. What I mean is I need all comments belonging to each picture as well as authors info. Thanks for your help!

Upvotes: 2

Views: 80

Answers (2)

Ignacio Ruiz
Ignacio Ruiz

Reputation: 621

As you've said, you can fetch the image info with your query, I'll extend it in order to fetch the user info too:

$select = $comments->select()->setIntegrityCheck(false)
                   ->from('comments')
                   ->joinInner('users', 'users.id = comments.user_id')
                   ->where('comments.image_id = ?', $this->view->imageChosen->id);

$this->view->commentsChosen = $comments->fetchAll($select);

The generated query would be:

SELECT comments.* users.* FROM comments
INNER JOIN users ON users.id = comments.user_id
WHERE comments.image_id = [Your_id_here]

I hope this helps!

Upvotes: 2

Peter
Peter

Reputation: 215

I've managed to get all comments belonging to each picture.

Controller:

$this->view->commentsChosen = $comments->fetchAll($comments->select()->where('image_id = ?', $this->view->imageChosen->id));

View:

for ($i=0; $i<count($this->commentsChosen); $i++) {
    echo $this->commentsChosen[$i]->comment;
    //Have 'user_id' only
    echo $this->commentsChosen[$i]->user_id;
}

However, I still can't get authors details.

Upvotes: 0

Related Questions