我已经检查了大多数相关的答案在这里的stackoverflow，但找不到什么适合我的情况。
任何友好的答复将不胜感激。
我使用PHPPDO从两个名为comments和comments_reply的mysql表中获取数据，值如下
注解表
- 注解ID
- 新闻ID
- 注解名称
- 评论日期
注解回复表
- 回复ID
- 注解ID
- 回复名称
- 答复
- 答复日期
我返回空数组作为结果，而不是像这样的JOSN格式的结果：
[
{"id": 1,
"comment_date": "2017-08-09",
"comment_time": "06:10:00",
"names": "Imenwo Alex",
"img": "c1.jpg",
"comments": "<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p><p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p>",
"replies":[
{
"id": 1,
"reply_date": "2017-08-09",
"reply_time": "06:10:00",
"names": "frank Alex",
"img": "c1.jpg",
"reply": "<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p><p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p>"
},
{
"id": 2,
"reply_date": "2017-08-09",
"reply_time": "06:10:00",
"names": "frank Alex",
"img": "c1.jpg",
"reply": "<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p><p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p>"
}
]
},
{"id": 2,
"comment_date": "2017-08-09",
"comment_time": "06:10:00",
"names": "Imenwo Alex",
"img": "c1.jpg",
"comments": "<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p><p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p>",
"replies":[
{
"id": 1,
"reply_date": "2017-08-09",
"reply_time": "06:10:00",
"names": "frank Alex",
"img": "c1.jpg",
"reply": "<p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p><p>Lorem ipsum dolor sit amet, consectetur adipisicing elit,There are many variations of passages of Lorem Ipsum available, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p>"
}
]
}
]下面是我的PHP脚本：
//
$q = "SELECT * FROM comments WHERE comments.news_id =:id";
//prepare
$stmt = $this->DB_con->prepare($q);
//bind parameters
$stmt->bindParam(':id', $id);
//create an array
$json_response = array();
while($obj=$stmt->fetch(PDO::FETCH_OBJ)) {
$currentComments = array(
'id' => $obj->comment_id,
'comment_names' => $obj->comment_names,
'comment_date' => $obj->comment_date,
'comment_time' => $obj->comment_time,
'comment_img' => $obj->comment_img,
'comments' => $obj->comments,
'replies' => array()
);
//
$r = "SELECT * FROM comments_reply WHERE comments_reply.comment_id =:id";
//prepare
$stmt = $this->DB_con->prepare($r);
//bind parameters
$stmt->bindParam(':id', $obj->comment_id);
while($obj=$stmt->fetch(PDO::FETCH_OBJ)) {
$currentComments['replies'][] = array(
'id' => $obj->reply_id,
'reply_names' => $obj->reply_names,
'reply_date' => $obj->reply_date,
'reply_time' => $obj->reply_time,
'reply_img' => $obj->reply_img,
'reply' => $obj->reply
);
}
array_push($json_response, $currentComments); //push the values in the array
}
return json_encode($json_response);
3条答案
按热度按时间syqv5f0l1#
不要覆盖$stmt和$obj。类似这样：
q43xntqr2#
你也可以在MySQL中这样做，为了保持简短，你可以这样做：
有关完整的（或更完整的😉）SQL，请参阅DBFIDDLE
结果：
dgsult0t3#
在第二个循环中，你必须使用array_push和另一个数组--类似这样。
]