Jump to content

Prepared Statement in PHP MVC


kringeltorte

Recommended Posts

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!! 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.