我已经在stackoverflow上检查了最相关的答案，但找不到适合我的情况的答案。
任何形式的答复将不胜感激。
我正在使用PHP PDO从两个名为comments和comments_reply的mysql表中获取数据，其值如下
评论表
comment_id
news_id
comment_names
comment_date
comment_reply表
Reply_id
comment_id
Reply_names
回复
回复日期
我返回空数组作为结果，而不是像这样的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);
最佳答案
不要覆盖$ stmt和$ obj。像这样：
$q = "SELECT * FROM comments WHERE comments.news_id =:id";
//prepare
$stmt = $this->DB_con->prepare($q);
$r = "SELECT * FROM comments_reply WHERE comments_reply.comment_id =:id";
//prepare
$stmt2 = $this->DB_con->prepare($r);
//execute with bound parameter
$stmt->execute( array(':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()
);
//
$stmt2->execute( array(':id'=> $obj->comment_id) );
while($obj2=$stmt2->fetch(PDO::FETCH_OBJ)) {
$currentComments['replies'][] = array(
'id' => $obj2->reply_id,
'reply_names' => $obj2->reply_names,
'reply_date' => $obj2->reply_date,
'reply_time' => $obj2->reply_time,
'reply_img' => $obj2->reply_img,
'reply' => $obj2->reply
);
}
array_push($json_response, $currentComments); //push the values in the array
}
return json_encode($json_response);