使用MYSQL和PHP循环出流后和相关注释(Loop out post stream and associated comments using MYSQL and PHP)

 我知道之前已经问了几次，但我已经尝试了这个网站上的所有答案，但我仍然没有在哪里。 
 
 我正在尝试创建一个Twitter类型的订阅源，它将循环播出帖子，然后为订阅源中的每个帖子循环出相关的评论（类似于Facebook）。 我不确定执行此操作的最佳方法是两个单独的查询还是一个查询中的所有查询以及如何将其全部循环？ 
 
 我的数据库结构如下： 
 
--TWEETS--              --COMMENTS--          --USERS--
id                      id                    id
accountno               accountno             accountno
userid                  userid                email
createdat               createdat             name
tweet                   tweetid 
image                   comment
 
 我的PHP函数如下： 
 
function gettweets(){

$result = mysql_query("SELECT tweets.id,tweets.tweet,tweets.createdat,tweets.userid,users.name,users.avatar,users.biog FROM tweets INNER JOIN users ON tweets.userid = users.id WHERE tweets.accountno ='123456' ORDER by tweets.ID DESC LIMIT 20");

while($row = mysql_fetch_array( $result )) {
  $name = $row['name'];
  $biog = $row['biog'];
  $avatar = $row['avatar'];
  $newtweet= $row['tweet'];
  $tweetid = $row['id'];
  $timeago = ago($row['createdat']);
  $thistweet = '';
  $thistweet .= "<div class='tweet' data-tweetid='$tweetid'>";
  $thistweet .= "<div class='left'>";
  $thistweet .= "<img width='45' height='45' src='$avatar' alt='placeholder+image'>";
  $thistweet .= "</div><!-- / -->";
  $thistweet .= "<div class='right'>";
  $thistweet .= "<span class='name'>$name says</span>";
  $thistweet .= "<p>";
  $thistweet .= "$newtweet";
  $thistweet .=  "</p>";
  $thistweet .= "<span class='replybar'> $timeago <a class='like' data-tweetid='$tweetid' href='' title=''>Like</a> | "; 
$thistweet .= "<a class='favourite' data-tweetid='$tweetid' href='' title=''>Favourite</a> | ";
$thistweet .= "<a class='mainreply' href='' title=''>Reply</a></span>";

//I WANT TO LOOP OUT THE COMMENTS HERE 


$thistweet .= "</div><!-- / --><span class='clear'></span></div><!--End Tweet -->";
return $thistweet;
} 

}
 
 **编辑* * * 
 
 我已经尝试了以下关于创建它的方法的答案，我现在已经成功地通过每条推文下面的相关评论来圈出“推文”。 但是我的问题是它正在为我的特定推文的每一条评论循环我的'推文'，即 
 
tweet 1
   -comment 1.1
tweet 1
   -comment 1.1
   -comment 1.2 (Tweet 1 has 2 comments so loops out tweet 1 two times)
tweet 2
   -comment 2.1
tweet 2
   -comment 2.1
   -comment 2.2
tweet 2
   -comment 2.1
   -comment 2.2
   -comment 2.3 (Tweet 2 has 3 comments so loops out tweet 2 three times)
 
 我认为这是我的SQL查询的问题，因为我是使用JOIN语句的新手。 这是我目前用于查询的内容 
 
  "SELECT tweets.accountno, tweets.id,tweets.tweet,tweets.createdat,tweets.userid,users.name,users.avatar,users.biog,comments.comment FROM tweets INNER JOIN users ON tweets.userid = users.id JOIN comments ON tweets.id = comments.tweetid WHERE tweets.accountno ='123456' ORDER by tweets.ID DESC LIMIT 20"
 
 有人能帮忙吗？ 非常感激？ 

I know this has been asked a couple of times before but I've tried all the answers on this site and am still getting no where.
 
I am trying to create a twitter type feed that will loop out the posts and then loop out the associated comments for each post in the feed (similar to Facebook). I'm not sure if the best way to do this would be two separate queries or all in one query and also how do I loop it all out?
 
My database structure is as follows:
 
--TWEETS--              --COMMENTS--          --USERS--
id                      id                    id
accountno               accountno             accountno
userid                  userid                email
createdat               createdat             name
tweet                   tweetid 
image                   comment
 
My PHP function is as follows:
 
function gettweets(){

$result = mysql_query("SELECT tweets.id,tweets.tweet,tweets.createdat,tweets.userid,users.name,users.avatar,users.biog FROM tweets INNER JOIN users ON tweets.userid = users.id WHERE tweets.accountno ='123456' ORDER by tweets.ID DESC LIMIT 20");

while($row = mysql_fetch_array( $result )) {
  $name = $row['name'];
  $biog = $row['biog'];
  $avatar = $row['avatar'];
  $newtweet= $row['tweet'];
  $tweetid = $row['id'];
  $timeago = ago($row['createdat']);
  $thistweet = '';
  $thistweet .= "<div class='tweet' data-tweetid='$tweetid'>";
  $thistweet .= "<div class='left'>";
  $thistweet .= "<img width='45' height='45' src='$avatar' alt='placeholder+image'>";
  $thistweet .= "</div><!-- / -->";
  $thistweet .= "<div class='right'>";
  $thistweet .= "<span class='name'>$name says</span>";
  $thistweet .= "<p>";
  $thistweet .= "$newtweet";
  $thistweet .=  "</p>";
  $thistweet .= "<span class='replybar'> $timeago <a class='like' data-tweetid='$tweetid' href='' title=''>Like</a> | "; 
$thistweet .= "<a class='favourite' data-tweetid='$tweetid' href='' title=''>Favourite</a> | ";
$thistweet .= "<a class='mainreply' href='' title=''>Reply</a></span>";

//I WANT TO LOOP OUT THE COMMENTS HERE 


$thistweet .= "</div><!-- / --><span class='clear'></span></div><!--End Tweet -->";
return $thistweet;
} 

}
 
**EDIT***
 
I've tried the below answer regarding the way to create it and I am now succesfully managing to loop out the 'tweets' with the associated comments below each tweet. However my problem is that it is looping out my 'tweets' for every number of comments that I have for that particular tweet ie
 
tweet 1
   -comment 1.1
tweet 1
   -comment 1.1
   -comment 1.2 (Tweet 1 has 2 comments so loops out tweet 1 two times)
tweet 2
   -comment 2.1
tweet 2
   -comment 2.1
   -comment 2.2
tweet 2
   -comment 2.1
   -comment 2.2
   -comment 2.3 (Tweet 2 has 3 comments so loops out tweet 2 three times)
 
I think this is a problem with my SQL query as Im new to using JOIN statements. This is what I currently have for my query
 
  "SELECT tweets.accountno, tweets.id,tweets.tweet,tweets.createdat,tweets.userid,users.name,users.avatar,users.biog,comments.comment FROM tweets INNER JOIN users ON tweets.userid = users.id JOIN comments ON tweets.id = comments.tweetid WHERE tweets.accountno ='123456' ORDER by tweets.ID DESC LIMIT 20"
 
Is anyone able to help? Much appreciated?

原文：https://stackoverflow.com/questions/10310855