Pulling data from database in CodeIgniter ( or any MVC platform)?

Question

I have this in Model:

public function index_loop() { 
        $post_user = $this->db->query("SELECT posts.post_title, posts.post_content, posts.post_date, users.username FROM posts LEFT JOIN users ON posts.user_id = users.user_id ORDER BY posts.post_ID DESC");
        //$categories = $this->db->query("SELECT categories.cat_name, categories.cat_id FROM  
        //$comments = $this->db->query("SELECT COUNT(comment_id) FROM comments WHERE
        return $post_user->result_array();
    }

What I need is to show categories for each post and comments (although I guess if I figure out categories passing than comments are the same way)

in View file:

 <?php foreach($posts as $zz) { ?>
         <div class="article">
           <h2><?php echo $zz['post_title']; ?></h2>
           <p>Posted by <a href=""><?php echo $zz['username']; ?></a> | Filed under <a href="#">templates</a>, <a href=>internet</a></p>
            <p><?php echo $zz['post_content']; ?></p>
           <p><a href=>Read more</a> | <a href=>Comments (5)</a> | <?php echo $zz['post_date']; ?></p>
        </div> <?php } ?>

So, if I want to loop categories on each blog I need that blogs ID, how do I get it if I make all queries from Model? The same for comments

Is it good that I make one big complicated DB query ( which is hard but I can do it) or I can do 2 or 3 separate smaller queries?

Answer 1

Codeigniter allows you to return database results as objects (model objects, for example), which makes data much easier to work with. You can issue your initial query to the Posts table, include the posts.id field in your result set, and pass the name of your Post_model class to the $db->query->result() function to tell codeigniter that you'd like your result(s) returned as instances of the Post_model class.

You can then define methods on the Post_model class to GetCategories by post_id and GetComments by post_id, and then call these methods to populate your $categories and $comments collections for each Post_model returned from your query.

Here's an example, i hope it helps:

    public class Post_model extends CI_Model
    {

        // All the properties in the Posts table, as well as a couple variables to hold the categories and comments for this Post:
        public $id;
        public $post_title;
        public $post_content;
        public $post_date;
        public $username;        
        public $categories;
        public $comments;

        public function index_loop() 
        { 
                return $this->GetAllPosts();
        }

        // function to get all posts from the database, including comments and categories.
        // returns an array of Post_model objects
        public function GetAllPosts()
        {
                // define an empty array to hold the results of you query.
                $all_posts = array();

                // define your sql query. NOTE the POSTS.ID field has been added to the field list
                $sql = "SELECT posts.id, 
                               posts.post_title, 
                               posts.post_content, 
                               posts.post_date,
                               users.username
                        FROM posts LEFT JOIN users ON posts.user_id = users.user_id
                        ORDER BY posts.post_id DESC";

                // issue the query
                $query = $this->db->query($sql);

                // loop through the query results, passing a string to result() which represents a class to instantiate 
                //for each result object (note: this class must be loaded)

                foreach($query->result("Post_model") as $post)
                {
                        $post->categories = $this->GetPostCategories($post->id);
                        $post->comments = $this->GetPostComments($post->id);
                        $all_posts[] = $post;
                }

                return $all_posts;
        }

        // function to return categories for a given post_id.
        // returns an array of Category_model objects.
        public function GetPostCategories($post_id)
        {
                $sql = "SELECT category.id, ... WHERE post_id = ?";
                $query = $this->db->query($sql, array($post_id));
                $categories = array();
                foreach($query->result("Category_model") as $category)
                {
                        $categories[] = $category;
                }
                return $categories;
        }

        // function to return comments for a given post_id. 
        //returns an array of Comment_model objects
        public function GetPostComments($post_id)
        {
                $sql = "SELECT comment.id, ... WHERE post_id = ?";
                $query = $this->db->query($sql, array($post_id));
                $comments = array();
                foreach($query->result("Comment_model") as $comment)
                {
                        $comments[] = $comment;
                }
                return $comments;
        }    
}

Then, in your view, you can access your $posts array as Post_model objects rather than as result_arrays:

<?php foreach($posts as $zz) { ?>
         <div class="article">
           <h2><?php echo $zz->post_title; ?></h2>
           <p>Posted by <a href=""><?php echo $zz->username; ?></a> | 

              Filed under 
               <?php foreach($zz->categories as $category) {
                        echo '<a href="#">{$category->name}</a>, ';
                     }
               ?>
           </p>
           <p><?php echo $zz->post_content; ?></p>
           <p><a href=>Read more</a> | <a href=>Comments (5)</a> | <?php echo $zz->post_date; ?></p>
        </div> <?php } ?>

As for the question of efficiency, it's going to depend on a lot of factors (is the database located on the same machine as the web server? how many posts are there? etc). A single large query will typically perform quicker than several smaller ones, but it will require profiling to really determine whether the performance gains are worth seeking. I always prefer to try and write readable/understandable code rather than optimize at the expense of adding complexity.

Answer 2

You can do 1 thing.

make different category table and comments table.

category_id will be linked with category_id column in post table.

In comment table, create column post_id which will be linked with post_id in post table.

then you can retrieve all the information at once by this extended query.

$post_user = $this->db->query("SELECT posts.post_title, posts.post_content, posts.post_date, users.username FROM posts LEFT JOIN users ON posts.user_id = users.user_id LEFT JOIN category ON category.category_id = post.category_id ORDER BY posts.post_ID DESC");

here you got the category.

For comment, i am not sure how you want the output as. one way is as Yan said. If you can be more specific, i can advice some simpler approach.