duanmangxie7131
2016-05-12 12:53
浏览 46
已采纳

从codeigniter中的多个表中获取数据？

i have two table, one is post table and other one is comment table.in which i am using post_id as foreign_key. Now i want to get, a post with all its comments. and response should be in this format.

 { 
    "posid":1,
    "post_name":"testpost",
    "comments":[{
                    "comment_id":1,
                    "comment_des":"testcoment"
                },{
                    "comment_id":2,
                    "comment_des":"testcoment2"
                }
            ]
    }

Can any one write simple SQL query for me for this type of response?

I tried below query in codeigniter , but this return multiple result, mean one post two time, because one post contains two comments.

  $this->db->select("p.post_id,p.post_desc,p.post_time ,c.id,c.comment_desc,c.comment_time");
  $this->db->join("asoc_comments as c","p.post_id = c.post_id","INNER");
 $response = $this->db->get("asgn_posts as p")->result();

图片转代码服务由CSDN问答提供 功能建议

我有两个表，一个是post表，另一个是comment table.in我使用post_id作为foreign_key 。 我现在想要的，一篇包含所有评论的帖子。 并且响应应该采用这种格式。 

  {
“posid”：1，
“post_name”：“testpost”，
“注释”：[{\  n“comment_id”：1，
“comment_des”：“testcoment”
}，{
“comment_id”：2，
“comment_des”：“testcoment2”
} 
] 
} 
 <\ <  / code>  
 
 
对于这种类型的响应，任何人都可以为我编写简单的SQL查询吗？ 
 
 
我在codeigniter中尝试了以下查询，但是这次返回 多个结果，意味着一个帖子两次，因为一个帖子包含两个评论。 
 
 
  $ this-＆gt; db-＆gt; select（“p.post_id，p.post_desc，  p.post_time，c.id，c.comment_desc，c.comment_time“）; 
 $ this-＆gt; db-＆gt; join（”asoc_comments as c“，”p.post_id = c.post_id“，”INNER“  ）; 
 $ response = $ this-＆gt; db-＆gt; get（“asgn_posts as p”） - ＆gt; result（）;
  • 写回答
  • 赞 踩
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dscizpq790832708 2016-05-12 13:05
    已采纳

    Active record example where we loop the results in order to format the output right without getting multiple result rows for each post:

    $q = $this->db->select('post_id,post_desc,post_time')->get('asgn_posts');
$data = array();
foreach ($q->results() as $p):
   $qc = $this->db->select('id,comment_desc,comment_time')->where('post_id',$p->post_id)->get('asoc_comments');
   $p->comments = $qc->results();
   $data[] = $p;
endforeach;
return $data;
    已采纳该答案
    赞 踩 打赏 评论
  • dongnai5905 2016-05-12 13:05

    You can write this query to show your result:

    SELECT p.post_id as 'posid',p.post_name,(SELECT c.id as 'comment_id' , c.comment_desc as 'comment_des' from asoc_comments c where c.post_id = p.post_id) as 'comments' from asgn_posts p

    E.g.

    $response=$this->db->query("SELECT p.post_id as 'posid',p.post_name,(SELECT c.id as 'comment_id' , c.comment_desc as 'comment_des' from asoc_comments c where c.post_id = p.post_id) as 'comments' from asgn_posts p")->result();
    赞 踩 打赏 评论
  • dsfs21312 2016-05-12 13:14

    You can Do this task by joining this two table using Row SQL or Active record.

    As 

    public function your_model_method(){
    $this->db->select('table_1.*,table_2.*')->from('table_1');
    $this->db->join('table_2','table_2.key_field=table_1.key_field');
    return $this->db->get()->result_array();
    }

    Hope it will help..

    Thank you!

    赞 踩 打赏 评论

报告相同问题？

提交

相关推荐 更多相似问题