php – 从CodeIgniter(或任何MVC平台)中的数据库中提取数据？

然后,您可以通过post_id将Post_model类的方法定义为GetCategories,通过post_id定义GetCments,然后调用这些方法为查询返回的每个Post_model填充$categories和$comments集合.

这是一个例子,我希望它有所帮助：

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,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,array($post_id));
                $comments = array();
                foreach($query->result("Comment_model") as $comment)
                {
                        $comments[] = $comment;
                }
                return $comments;
        }    
}

然后,在您的视图中,您可以将$posts数组作为Post_model对象而不是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 } ?>

至于效率问题,它将取决于很多因素(数据库与网络服务器位于同一台机器上？有多少帖子？等等).单个大型查询通常比几个较小的查询执行速度快,但需要进行性能分析才能真正确定性能增益是否值得寻求.我总是喜欢尝试编写可读/可理解的代码,而不是以增加复杂性为代价进行优化.