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();
  • 写回答

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;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 帮我解决一下膳食平衡的线性规划模型的数据实例
  • ¥40 万年历缺少农历,需要和阳历同时显示
  • ¥250 雷电模拟器内存穿透、寻基址和特征码的教学
  • ¥200 比特币ord程序wallet_constructor.rs文件支持一次性铸造1000个代币,并将它们分配到40个UTXO上(每个UTXO上分配25个代币),并设置找零地址
  • ¥15 关于Java的学习问题
  • ¥15 如何使用chatgpt完成文本分类任务?
  • ¥15 已知速度v关于位置s的等式,怎么转化为已知位置求速度v的等式
  • ¥15 我有个餐饮系统,用wampserver把环境配置好了,但是后端的网页却进去,是为什么,能不能帮远程一下?
  • ¥15 R运行没有名称为"species"的插槽对于此对象类"SDMmodelCV"
  • ¥20 基于决策树的数字信号处理,2ask 2psk 2fsk的代码,检查下报错的原因