使用PHP显示mysql的输出（json编码）

Question

I'm trying to code a page like facebook-view_posts_page where I need to show the result as POST 1..Comment 1.. Comment 2.. POST 2.. Comment 3

The output of my code is

POST 1
POST 2
comment 1
comment 2
comment 3

How should I re write my code?

<?php
include("connect.php");

$userID=$_REQUEST['userID'];

$Query=("select * from tb_post where userID='$userID'");
$result=mysql_query($Query);
$count=mysql_num_rows($result);


if($count>0)
{
//$post['result']="sucess";

$joinQuery=("select * from tb_post where tb_post.userID='$userID'");
$joinResult=mysql_query($joinQuery);


    while($row=mysql_fetch_assoc($joinResult))
    {

        $posts[]=$row;


            $postid=$row['postID'];
            $commentQuery=("select tb_comment.commentID,tb_comment.userID ,tb_comment.postID ,tb_comment.comment ,tb_comment.date,signup.userName,signup.image from tb_comment,signup where tb_comment.postID='$postid' and signup.userID=tb_comment.userID");
            $commentResult=mysql_query($commentQuery);
                //$post['posts']=$posts;

                while($commentrow=mysql_fetch_assoc($commentResult))
                {

                $comments[]=$commentrow;


                }
    }
    $post=array("result"=>"success","posts"=>$posts,"comments"=>$comments);

}
else
{
    $post['result']="failed";
    $post['error']="no data found";
}
$data='content-type:application/json';
$data=json_encode($post);
echo $data;


?>

Answer 1

I will explain you the approach.

You are fetching and appending comments in an array without any direct key reference to the post.

array comments;
while (comment = fetch comments) {
  comments[post id][] = comment;
}

while showing:

while (post = fetch posts) {
  echo post title;
  foreach (comments[post id] as postComment) {
    echo postComment;
  }
}

Comments should have reference to the post as key of comments array.

In your case, you are already fetching comments correctly, just change the following inner while loop to:

while($commentrow=mysql_fetch_assoc($commentResult)) {
  $comments[$commentrow['postID'][]=$commentrow; // Observe postID
}

Fully working code:

<?php
include("connect.php");
$userID=$_REQUEST['userID'];
$Query=("select * from tb_post where userID='$userID'");
$result=mysql_query($Query);
$count=mysql_num_rows($result);
if($count>0) {
  $joinQuery=("select * from tb_post where tb_post.userID='$userID'");
  $joinResult=mysql_query($joinQuery);
  while($row=mysql_fetch_assoc($joinResult)) {
    $posts[]=$row;
    $postid=$row['postID'];
    $commentQuery=("select tb_comment.commentID,tb_comment.userID ,tb_comment.postID ,
      tb_comment.comment ,tb_comment.date,signup.userName,signup.image 
      from tb_comment,signup 
      where tb_comment.postID='$postid' and signup.userID=tb_comment.userID");
    $commentResult=mysql_query($commentQuery);
    while($commentrow=mysql_fetch_assoc($commentResult)) {
      $comments[$commentrow['postID']][] = $commentrow;
    }
  }
  $post=array("result"=>"success","posts"=>$posts,"comments"=>$comments);
}
else {
  $post['result']="failed";
  $post['error']="no data found";
}
$data='content-type:application/json';
$data=json_encode($post);
echo $data;
?>

You can print posts and its comments like this:

<?php
if (! empty($posts)) {
  foreach ($posts as $post) {
    echo $post['post_title_field'] . "<br/>";
    if (! empty($comments[$post['postID']])) {
      foreach ($comments[$post['postID']] as $postComment) {
        echo postComment . "<br/>";
      }
    }
  }
}
?>