根据ID列出来自不同表，组的数据

Question

I am beginner in MySQL. I have question about displaying data from two different table (same database), and list them based on their ID.

Recently, I am doing my project about creating simple discussion board (chat style) with features another user can comment to a post.

I have 2 tables,

Shoutbox -> Record a Discussion submit by an user

ID | Name | Text  |
1 | Iqbal  | This is Question   |
2 | Zizan | Another Question |

Comment ->> Record a Comment from another user

ID | Comment   |
1 | Answer for Iqbal |
1 | Another answer for Iqbal   |
2 | Answer for Zizan |

How do I display Comment, according to Data in Shoutbox (based on their ID) ?

So, it should like this

Discussion Board

Iqbal : This is Question
Answer : Answer for Iqbal
Answer : Another Answer for Iqbal

Zizan : Another Question
Answer : Answer for Zizan

Answer 1

Try this

SELECT * FROM `Shoutbox`
LEFT JOIN `Comment` 
ON `Shoutbox`.ID = `Comment`.ID

Of course to show data you will have a while loop do this in your while

while ( ... ) {

   echo $r['Name'] . ' : ' . $r['Text'];
   echo '<br />';
   echo 'Answer :' . $r['Comment'];

   echo '<br /> <br />';
}

Edit:

If you have multiple Answers against each Shoutbox then you need to do the following.

Notice the change in the query

SELECT * FROM `Shoutbox`
RIGHT JOIN `Comment` 
ON `Shoutbox`.ID = `Comment`.ID

and the following code

$finaleArray = array();
while ( $r = mysqli_fetch_array($result) ) {

   $id = $r['ID'];

   if ( !isset($finaleArray[$id]['question']) ) {
      $finaleArray[$id]['question'] = $r['Text'];
      $finaleArray[$id]['name'] = $r['Name'];
   }

   $finaleArray[$id]['answer'][] = $r['Comment'];
}

foreach( $finaleArray as $id => $a ) {
   echo $a['name'] . ' : ' . $a['question'];
   foreach($a['answer'] as $ans) {
      echo '<br />';
      echo 'Answer :' . $ans;
   }
   echo '<br /> <br />';
}

Of course there are confusions in the above code, you should ask if you don't understand anything.