Loop out post stream and associated comments using MYSQL and PHP

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?

Solution

First, I'd make sure your query returns what you expect. A good tool for this is phpMyAdmin.

But if I understand your question right, you want something like:

Tweet1
    Comment1 about Tweet1
    Comment2 about Tweet1
Tweet2
    Comment1 about Tweet2
    Comment2 about Tweet2
    Comment3 about Tweet2
Tweet3
    Comment1 about Tweet3
(etc.)

If so, there are two main ways to do this:

The simple, less efficient way

Write 2 queries, an outer query and an inner query that uses the tweetid from the outer query.

It might look something like:

$qo = "SELECT * FROM tweet ORDER by tweets.ID"
$ro = mysql_query($qo);
while($ao = mysql_fetch_array($ro)
{
    // DISPLAY THE TWEET HERE
    $qi = sprintf("SELECT * FROM comments WHERE tweetid = %d", ao['id']);
    $ri = mysql_query($qi);
    while($ai = mysql_fetch_array($ri)
    {
        //DISPLAY THE COMMENT HERE
    }
}

The more complex, more efficient way

Use a join like you're doing, then loop through the results, keeping track whether the tweet ID has changed. If it has, display the tweet.

So, assuming your query is right:

$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";
$result = mysql_query($result);

$tweetid = -1;  // Some impossible tweetid
while($row = mysql_fetch_array( $result )) {
    if($row['id'] != $tweetid){ 
        // PRINT TWEET
        $tweetid = $row['id'];
    }
    // PRINT COMMENT
}

Either way will get you where you want, the first easier but involves more queries, the second more complex but more efficient.

Also, a final note about sorting based on table index -- this is almost never what you want to do. You should probably add a timestamp field that is either set through PHP or the SQL NOW() function.