使用PHP(json编码)显示mysql的输出


To display output from mysql using PHP (json encode)

我正在尝试编写一个类似facebook-view_posts_page的页面,其中我需要将结果显示为POST 1.Comment 1.Comment 2.POST 2.Comment 3.

我的代码输出是

POST 1
POST 2
comment 1
comment 2
comment 3

我应该如何重写代码?

<?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;

?>

我将向您解释该方法。

您在一个数组中获取和附加注释,而没有任何对帖子的直接键引用。

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

同时显示:

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

评论应该引用帖子作为评论数组的关键字。

在您的情况下,您已经正确地获取了注释,只需将以下内部while循环更改为:

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

完全工作代码:

<?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;
?>

你可以这样打印帖子及其评论:

<?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/>";
      }
    }
  }
}
?>

感谢大家的帮助。我的朋友提出了另一个解决方案,那就是我正在寻找的代码。这是代码:

<?php
    include("connect.php");
    $sel_post=mysql_query("SELECT * FROM tb_post WHERE userID='".$_REQUEST['userID']."'");
    if(mysql_num_rows($sel_post)>0)
    {
        while($row=mysql_fetch_assoc($sel_post))
        {
            $sel_post_owner=mysql_query("SELECT name,image FROM signup WHERE userID='".$row['userID']."'");
            if(mysql_num_rows($sel_post_owner)>0)
            {
                while($row_owner=mysql_fetch_array($sel_post_owner))
                {
                    $row['post_owner_name']=$row_owner['name'];
                    $row['post_owner_image']=$row_owner['image'];
                }
            }
            else
            {
                    $row['post_owner_name']="";
                    $row['post_owner_image']="";
            }
            $sel_comments=mysql_query("SELECT * FROM tb_comment WHERE postID='".$row['postID']."'");
            if(mysql_num_rows($sel_comments)>0)
            {
                $comments=array();
                while($row_comment=mysql_fetch_assoc($sel_comments))
                {
                    $sel_comment_owner=mysql_query("SELECT name,image FROM signup WHERE userID='".$row_comment['userID']."'");
                    if(mysql_num_rows($sel_post_owner)>0)
                    {
                        while($row_comment_owner=mysql_fetch_array($sel_comment_owner))
                        {
                            $row_comment['comment_owner_name']=$row_comment_owner['name'];
                            $row_comment['comment_owner_image']=$row_comment_owner['image'];
                        }
                    }
                    else
                    {
                            $row_comment['comment_owner_name']="";
                            $row_comment['comment_owner_image']="";
                    }
                    $comments[]=$row_comment;
                }
                $row['comments']=$comments;
            }
            else
            {
                $row['comments']=array();
            }
            $Post[]=$row;
        }
        $post=array("result"=>"success","Posts"=>$Post);
    }
    else
    {
        $post['result']="failed";
        $post['error']="no data found";
    }
    $data='content-type:application/json';
    $data=json_encode($post);
    echo $data;
    ?>