Databases
StackOverflow2013
Postsdbo
POLoop out post stream and associated comments using MYSQL and PHP
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLoop out post stream and associated comments using MYSQL and PHP
    primarykey
    data
    text
    <p>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.</p> <p>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?</p> <p>My database structure is as follows:</p> <pre><code>--TWEETS-- --COMMENTS-- --USERS-- id id id accountno accountno accountno userid userid email createdat createdat name tweet tweetid image comment </code></pre> <p>My PHP function is as follows:</p> <pre><code>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 .= "&lt;div class='tweet' data-tweetid='$tweetid'&gt;"; $thistweet .= "&lt;div class='left'&gt;"; $thistweet .= "&lt;img width='45' height='45' src='$avatar' alt='placeholder+image'&gt;"; $thistweet .= "&lt;/div&gt;&lt;!-- / --&gt;"; $thistweet .= "&lt;div class='right'&gt;"; $thistweet .= "&lt;span class='name'&gt;$name says&lt;/span&gt;"; $thistweet .= "&lt;p&gt;"; $thistweet .= "$newtweet"; $thistweet .= "&lt;/p&gt;"; $thistweet .= "&lt;span class='replybar'&gt; $timeago &lt;a class='like' data-tweetid='$tweetid' href='' title=''&gt;Like&lt;/a&gt; | "; $thistweet .= "&lt;a class='favourite' data-tweetid='$tweetid' href='' title=''&gt;Favourite&lt;/a&gt; | "; $thistweet .= "&lt;a class='mainreply' href='' title=''&gt;Reply&lt;/a&gt;&lt;/span&gt;"; //I WANT TO LOOP OUT THE COMMENTS HERE $thistweet .= "&lt;/div&gt;&lt;!-- / --&gt;&lt;span class='clear'&gt;&lt;/span&gt;&lt;/div&gt;&lt;!--End Tweet --&gt;"; return $thistweet; } } </code></pre> <p><em><strong></em>**EDIT*<em>*</em>*</strong></p> <p>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</p> <pre><code>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) </code></pre> <p>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</p> <pre><code> "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" </code></pre> <p>Is anyone able to help? Much appreciated?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAaron Lumsden
    1. USAaron Lumsden
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLoop out post stream and associated comments using MYSQL and PHP
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLoop out post stream and associated comments using MYSQL and PHP
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload