Prepared Statement in PHP MVC

[kringeltorte]

I am trying to create a simple forum in a MVC architecture.

This is my database setup (the relevant part):

Table: forum_categories

 

   

 `forum_categories` (

    `cat_id` INT( NOT NULL AUTO_INCREMENT,

    `cat_title` VARCHAR(255) NOT NULL,

    `cat_desc` TEXT NOT NULL,

    PRIMARY KEY (`cat_id`),

    UNIQUE KEY (`cat_title`)

 

Table: forum_topics

 

    

`forum_topics` (

    `topic_id` INT( NOT NULL AUTO_INCREMENT,

    `cat_id` INT( NOT NULL COMMENT 'foreign key with forum_categories table',

    `user_id` INT(11) NOT NULL COMMENT 'foreign key with users table',

    `topic_title` VARCHAR(255) NOT NULL,

    `topic_desc` TEXT NOT NULL, 

    `topic_date` DATETIME DEFAULT NULL,

    PRIMARY KEY (`topic_id`),

    FOREIGN KEY (`cat_id`) REFERENCES forum_categories (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE

Example of the functionality, I would like to achieve:

Category 1 has cat_id = 1

Category 2 has cat_id = 2

 

 

Topic 1 has cat_id = 1

Topic 2 has cat_id = 2

 

 

Now when category 1 is selected I just want topic 1 to show.

If category2 is selected I just want topic 2 to show.

This prepared SQL statement achieves that:

 

   

 PREPARE stmnt FROM 

        'SELECT * 

        FROM forum_categories fc

        JOIN forum_topics ft ON fc.cat_id = ft.cat_id

        WHERE fc.cat_id = ?

        ORDER BY ft.topic_date DESC';

    

    SET @a = 1;

    EXECUTE stmnt USING @a;

My Problem: I would like to move this functionality into my PHP MVC structure.

Here is my attempt, which does not work (it shows all topics in all categories).

Controller

 

   

 /**

    * Show all the topics in the chosen category

    */

    public function showForumTopics()

    {

        $topic_model = $this->loadModel('Forum');

        $this->view->forum_topics = $topic_model->getForumTopics();

        $this->view->render('forum/viewTopics');

    }

Model

 

   

 /**

    * Gets an array that contains all the forum topics in the database.

    * Each array element is an object, containing a specific topic's data.

    * @return array All the forum topics

    */

    public function getForumTopics($cat_id)

    {

         $sql = 'SELECT * FROM forum_categories fc JOIN forum_topics ft ON fc.cat_id = ft.cat_id WHERE fc.cat_id = :cat_id ORDER BY ft.topic_date DESC';

         $query = $this->db->prepare($sql);

         $query->execute(array(':cat_id' => $cat_id));

    

         return $query->fetchAll();

    }

 

View

 

  

  if ($this->forum_topics) {

                foreach($this->forum_topics as $key => $value) {

                    echo '<p><strong>Title:</strong>' . $value->topic_title . '</p>';

                    echo '<p><strong>Description:</strong> ' . $value->topic_desc . '</p>';

                    echo '<p><strong>Author:</strong> ' . $value->topic_author . '</p>';

                    echo '<p><strong>Date:</strong> ' . $value->topic_date . '</p>';

                }

            } else {

                echo 'No forum topics.';

            }

 

Help would be highly appreciated! Thank you!! 

[Ch0cu3r]

How is the category id being passed to your model? You dont appear to be passing it when you call getForumTopics()

$this->view->forum_topics = $topic_model->getForumTopics(); // <-- category id should be passed as an argument here