Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First, I'd make sure your query returns what you expect. A good tool for this is phpMyAdmin.</p> <p>But if I understand your question right, you want something like:</p> <pre><code>Tweet1 Comment1 about Tweet1 Comment2 about Tweet1 Tweet2 Comment1 about Tweet2 Comment2 about Tweet2 Comment3 about Tweet2 Tweet3 Comment1 about Tweet3 (etc.) </code></pre> <p>If so, there are two main ways to do this:</p> <p><strong>The simple, less efficient way</strong></p> <p>Write 2 queries, an outer query and an inner query that uses the tweetid from the outer query.</p> <p>It might look something like:</p> <pre><code>$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 } } </code></pre> <p><strong>The more complex, more efficient way</strong></p> <p>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.</p> <p>So, assuming your query is right:</p> <pre><code>$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 } </code></pre> <p>Either way will get you where you want, the first easier but involves more queries, the second more complex but more efficient.</p> <p>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.</p>
    singulars
    1. This table or related slice is empty.
    1. POLoop out post stream and associated comments using MYSQL and PHP
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USjedwards
    1. USjedwards
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POLoop out post stream and associated comments using MYSQL and PHP
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for this. It seems to make sense. I'll try the 2nd option later when I get home. I think I may have to rework the SQL Query though as I need it to get from three different tables. Users, tweets and comments. Thanks :-)
      singulars
      1. PO
      1. USAaron Lumsden
    2. COany ideas please?
      singulars
      1. PO
      1. USAaron Lumsden
    3. COAre you saying that you do or do not want the tweet to be displayed multiple times in the event that it has multiple comments. For example, if a tweet has 3 comments, do you want it to go `TWEET,COMMENT1,COMMENT2,COMMENT3` or `TWEET,COMMENT1,TWEET,COMMENT2,TWEET,COMMENT3`?
      singulars
      1. PO
      1. USjedwards
 

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