Geek asks and answers

Output message flow and related comments using MYSQL and PHP

I know this has been asked a couple of times, but I have tried all the answers on this site and still do not get it.

I am trying to create a twitter feed that will loop through the posts and then loop over the related comments for each post in the feed (like Facebook). I'm not sure if the best way to do this is with two separate requests or all in one request, and also how to do it all?

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 ***

, , "" . , "" , , ..

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- Im new 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"

- ? ?

+3
1

-, , , . phpMyAdmin.

, - :

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

, :

,

2 , , tweetid .

:

$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
    }
}

,

, , , . , .

, :

$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
}

, , , , - , .

, - , . , , PHP, SQL NOW().

+2

More articles:


All Articles