从2个表中提取信息 [英] Pulling information from 2 tables

问题描述

我想为我的新闻文章页面的嵌套查询一些帮助 - 基本上我想让每篇文章有相关的评论显示在下面，但目前它只返回一个评论每个文章：（

  function get_records（$ limit，$ offset，$ sort）{
 $ this-> db-> select ，COUNT（comments.news_id）作为注释，comments.comment为注释，news.id为id，news.created_on为created_on，CONCAT（users.firstname，，users.surname）为author，categories.category为category， （'news'，'comments'）; 
 $ this-> db-> join（'new'，'comment'）;'new_style.type as new_type'，FALSE）; 
 $ this-& users'，'users.id = news.author'，'left'）; 
 $ this-> db-> join（'comments'，'comments.news_id = news.id'，'left' ）; 
 $ this-> db-> join（'categories'，'categories.id = news.category'，'left'）; 
 $ this-> db-> join （'news_types'，'news_types.id = news.news_type'，'left'）; 
 $ this-> db-> group_by（'news.id'）; 
 $ this-> ; db-> order_by（'news.id'，'DESC'）; 
 $ this-> db-> limit（$ limit，$ offset）; 
 $ query = $ this-> db-> get（）; 
 if（$ query-> num_rows（）> 0）{
 return $ query-> result_array（）; 
} 
} 
  

解决方案

p> $ this-> db-> group_by（'news.id'）;


GROUP BY每个新闻项目只会向您返回一条记录，这就是为什么您只能获得一条评论。你将需要第二个查询获取所有的评论或删除GROUP BY以获得所有的评论与冗余的新闻项目信息（这真的不是一个好主意）。

I would like some help with my nested query for my news article page - basically I want each article to have its related comments displayed underneath but at the moment it only returns one comment for each article :(

function get_records($limit, $offset, $sort) {
   $this->db->select('news.*, COUNT(comments.news_id) as comments, comments.comment as comment, news.id as id, news.created_on as created_on, CONCAT(users.firstname, " ", users.surname) as author, categories.category as category, news_types.type as news_type', FALSE);
   $this->db->from('news', 'comments');
   $this->db->join('users', 'users.id = news.author', 'left');
   $this->db->join('comments', 'comments.news_id = news.id', 'left');
   $this->db->join('categories', 'categories.id = news.category', 'left');
   $this->db->join('news_types', 'news_types.id = news.news_type', 'left');
   $this->db->group_by('news.id');
   $this->db->order_by('news.id', 'DESC');
   $this->db->limit($limit, $offset);
   $query = $this->db->get();
   if($query->num_rows() > 0) {
      return $query->result_array();
   }
}  

解决方案

 $this->db->group_by('news.id');

GROUP BY will return only one record per news item to you, that is why you only get one comment. You will need to have a second query fetch all the comments OR remove the GROUP BY to get all the comments with the redundant news item information (which really isn't a good idea).

这篇关于从2个表中提取信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！