Zend Framework query db and getParam

Question

At the moment I have a page where I have retrieved information on a club by the id of that club. I now have a comments box where I want to retrieve the comments about that club, in the comments table I have the club_id and the parameter "club_id" is passed into this page. At the moment I am retrieving all of the comments from the table but I want just the comments for that club. A point in the right direction would be great!

Controller:

class ClubDescriptionController extends Zend_Controller_Action
{

public $auth = null;

public function init()
{
    $this->auth=Zend_Auth::getInstance();
}

http://pastebin.com/m66Sg26x
protected function authoriseUser()
{
    if (!$this->auth->hasIdentity()) {
            $route = array('controller'=>'auth', 'action'=>'index');
            $this->_helper->redirector->gotoRoute($route);

        }
    }
}

Model:

class Application_Model_DbTable_Comments extends Zend_Db_Table_Abstract
{

protected $_name = 'comments';

public function getComment($id) {
    $id = (int) $id;
    $row = $this->fetchRow('id = ' . $id);
    if (!$row) {
        throw new Exception("Count not find row $id");
    }
    return $row->toArray();
}

public function addComment($comment, $club_id) {
    $data = array(
        'comment' => $comment,
        'club_id' => $club_id,
        'comment_date' => new Zend_Db_Expr('NOW()'),
        );
    $this->insert($data);
    }   

    public function deleteComment($id) {
    $this->delete('id =' . (int) $id);
    }
}

The view:

<div id="view-comments">
        <?php foreach($this->comments as $comments) : ?>
            <p id="individual-comment">
                <?php echo $this->escape($comments->comment);?> - 
                <i><?php echo $this->escape($comments->comment_date);?></i>
            </p>
        <?php endforeach; ?>
</div>

I realise I am going to have to use the getComment(); function in my model and query it by the id but I'm getting confused on exactly how...

Thanks

Could you show the code in Application_Model_DbTable_Clubs? I think it will be relevant to your question. — vascowhite, Apr 24 '12 at 19:22

Ibrahim Azhar Armar · Answer 1 · 2012-04-24T18:12:09.773

0

In your controller you are calling

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

it should be

$this->view->comments = $comments->getComment($this->_request->getParam('club_id'));

where id variable will be fetched from url.

edited Apr 24 '12 at 18:12

answered Apr 24 '12 at 17:40

Ibrahim Azhar Armar

25,288
35
131
207

I thought that should of worked but I get the error -> http://pastebin.com/Dj00Kq8A Oh and the param is "club_id" to avoid confusion.. – Rex89 Apr 24 '12 at 17:46
with the controller updated you will see I could change -> $this->view->comments = $comments->getComment($this->_request->getParam('club_id')); to $this->view->comments = $comments->getComment($id); Sorry for the lack of code. – Rex89 Apr 24 '12 at 18:02
try this `$id = (int)$this->_request->getParam(’club_id’, 0);` – Ibrahim Azhar Armar Apr 24 '12 at 18:12
make sure the row you are trying to access exist in the database. and i am seeing some flaw in your logic now. since club_id should be the foreign key in comments table, you should be fetching the row by foreign key not primary key. if this still doesn't help you. post your db table for comments and club. – Ibrahim Azhar Armar Apr 24 '12 at 18:17
change `$row = $this->fetchRow('club_id = ' . $id)`; and check. – Ibrahim Azhar Armar Apr 24 '12 at 18:18
I get trying to get property of non-object, but the rows are definitely there in the table. -> http://pastebin.com/fDMDKD9k – Rex89 Apr 24 '12 at 18:24
clubs table -> http://pastebin.com/v3KT55j2 Comments table -> http://pastebin.com/00e7iLDr – Rex89 Apr 24 '12 at 18:27
just for debugging purpose try checking what sql query is being genrated by adding this in your model `die($row->__toString());` add it right below `$row = $this->fetchRow('club_id = ' . $id);` and let me know what is the output. – Ibrahim Azhar Armar Apr 24 '12 at 18:28
it doesn't recognise the method die($row->__toString()); – Rex89 Apr 24 '12 at 18:35
or try `echo $row->__toString();` or find a way to check what sql query is being generated. sorry i cannot help you much in this because i mysql do not use Zend_Db_Table instead i use doctrine :). – Ibrahim Azhar Armar Apr 24 '12 at 18:39
yeah, this seems to be an issue, I tried this in the model but it didn't work http://stackoverflow.com/questions/7723657/how-to-print-exact-sql-query-in-zend-framework – Rex89 Apr 24 '12 at 18:45
I will confer with a colleague tomorrow and hopefully I will be back with an answer. – Rex89 Apr 24 '12 at 18:59

score 0 · Accepted Answer · answered Apr 24 '12 at 19:45

It's been a while since I used Db_Table but I think you want to create a select object, which allows you to build a query that will select comments with the correct club_id:

$comments = new Application_Model_DbTable_Comments();
$select = $comments->select();
$select->where('club_id = ?', $id);

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

you may want to order the comments by date, if so, you can do this by adding an order clause to the select:

$select->order('comment_date ASC');

take a look at the docs for Zend_Db_Table_Select, which has quite a few examples: http://framework.zend.com/manual/en/zend.db.table.html#zend.db.table.fetch-all

Correct, I was not aware you could do it like this! Thanks! – Rex89 Apr 24 '12 at 22:18 — Rex89, Apr 24 '12 at 22:18

score 0 · Answer 3 · answered Apr 25 '12 at 19:06

Here is the working controller:

public function indexAction() {
    //authorisation
    $this->authoriseUser();
    //to get the paramter club_id to query for specific club information
    $id = (int) $this->_request->getParam('club_id', 0);

    //submit a comment
    $form = new Application_Form_Comment();
    $form->submit->setLabel('Comment');
    $this->view->form = $form;
    if ($this->getRequest()->isPost()) {
        $formData = $this->getRequest()->getPost();
        if ($form->isValid($formData)) {
            $comment = new Application_Model_DbTable_Comments();
            $comment->addComment($formData['comment'], $id);
        } else {
            $form->populate($formData);
        }
    }

    //initialise table
    $clubs = new Application_Model_DbTable_Clubs();
    $clubs = $clubs->getClub($id);
    $this->view->clubs = $clubs;

    //to get the comments for the club
    $comments = new Application_Model_DbTable_Comments();
    $select = $comments->select();
    $select->where('club_id = ?', $id);
    $select->order('comment_date ASC');

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

Zend Framework query db and getParam

3 Answers3