Query Issue - Trying to group comments within a discussion

I've spent the last few days wrestling with a MYSQL query issue. I hope someone can point me in the right direction.

I'm querying two tables ('questions' and 'comments') with the goal of returning the following layout:

Question 1

• Comment 1
• Comment 2
• Comment 3

Question 2

• Comment 4
• Comment 5

And so on...

Comments are unique to a question (i.e. comments live under a parent question).

My query (which I know is incorrect) looks like this:

<?php
     $query = "SELECT discussion.*, comments.* FROM discussion LEFT JOIN comments ON discussion.referenceID = comments.commentID";
     $result = mysql_query($query) or die(mysql_error());
     while($row = mysql_fetch_array($result)){
     echo $row['question']. " - ". $row['comme开发者_高级运维nt']. "<br /><br />";
}
?>

The result:

Question 1 - Comment 1 Question 1 - Comment 3 Question 2 - Comment 2

I'm close, but can't achieve the multiple comments under the single question. I tried a 'GROUP BY discussion.question' but that limited my results to:

Question 1 - Comment 1 Question 2 - Comment 2

To put it in context I'm trying to allow users to submit comments on multiple questions displayed on a single page.

Thanks in advance.

RR

try this:

$query = "SELECT * 
FROM discussion, comments
WHERE discussion.referenceID = comments.commentID
GROUP BY discussion.referenceID;

Just store the previous question and compare it to the current.

Sketch:

$prev_question = null;
while($row = mysql_fetch_array($result)) {
    if ($prev_question != $row['question']) {
        echo '<b>' . $row['question'] . '<b><br />';
        $prev_question = $row['question'];
    }
    echo $row['comment'] . '<br />';
}

Zerkms, brilliant! Thanks for the response. Works perfectly, aside from one issue.

The questions only appear if there is a comment associated (an issue I caused myself, at some point). The goal is to display all comments regardless of if comments are assigned.

Working code:

<?php
$query = "SELECT discussion.*, comments.* FROM discussion LEFT JOIN comments ON discussion.referenceID = comments.commentID";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$prev_question = null;
while($row = mysql_fetch_array($result)) {
if ($prev_question != $row['question']) {
echo "<h3>" . $row['question'] . "</h3>";
$prev_question = $row['question'];
}
echo "<img src=\"images/bg_addform_topFull.gif\" width=\"446\" height=\"11\" /><div class=\"comment\"><h3>" . $row['author'] . "</h3><div class=\"greyText\">" . $row['dt'] . "</div><p>" . $row['comment'] . "</p></div><img src=\"images/bg_addform_bottom.gif\" width=\"446\" height=\"11\" class=\"footerImage\" />";
        }
        }
    ?>

Again, thanks for the help.