Left join grouped by username

Grouping a set of results by left join

[s9901470]

I have three tables in a questionnaire

Answer (for data e.g. strongly agree)
Question (for questions e.g. Do you agree?)
Answer choice (for radio button answer labels e.g. strongly agree)

I want to print a set of results for each person, identified by Answer.UID.

The code below prints everyone's results, but I want to group these by Answer.UID and print the UID at the start of each set of results.

Can anyone suggest how I modify this code?

Thank you in advance.

$query = "SELECT
Answer.QID, Answer.Answer, Answer.UID,
AnswerChoice.QID, AnswerChoice.Label,
Question.QID
FROM Answer
LEFT JOIN AnswerChoice ON Answer.QID=AnswerChoice.QID
LEFT JOIN Question ON Answer.QID=Question.QID
";

$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row separated by commas
$out = ''; // initialize
while($row = mysql_fetch_array($result)) {
echo ' '.$row['Label'].', ';
}

?>

[Scally_Ally]

would sometyhing like this help

$query = "SELECT
Answer.QID, Answer.Answer, Answer.UID,
AnswerChoice.QID, AnswerChoice.Label,
Question.QID
FROM Answer
LEFT JOIN AnswerChoice ON Answer.QID=AnswerChoice.QID
LEFT JOIN Question ON Answer.QID=Question.QID
ORDER BY Answer.UID
";

This will group all users together, you can then use an if statement to figure out if the id of the row you are printing out has changed

$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row separated by commas
$out = ''; // initialize
$lastID=0; //initialise
while($row = mysql_fetch_array($result)) {
if($row['UID']!=$lastID) echo 'UID, ' . $row['UID'];
echo ' '.$row['Label'].', ';
$lastID = $row['UID'];
}

Not the most elegant way to do it i suppose but it might work.

Ally

[ramoneguru]

A "GROUP BY Answer.UID" didn't work? Note, you may have to GROUP BY AnswerUID, everything else in the select statement.
--Nick

[s9901470]

Thank you! That works. Now, slightly more complicated, I want to print a list of the questions at the top of the page (so I can export to excel).

So I also need to select Question.Question.

How can I add this to the code to print one line of the questions, corresponding to all the answers?

$query = "SELECT
Answer.QID, Answer.Answer, Answer.UID,
AnswerChoice.QID, AnswerChoice.Label,
Question.QID, Question.Question
FROM Answer
LEFT JOIN AnswerChoice ON Answer.QID=AnswerChoice.QID
LEFT JOIN Question ON Answer.QID=Question.QID
";

[s9901470]

I also have another problem.

The Answer data is stored as an ID for questions with radio buttons, but as text for questions which are text based. So the bit I want is in two different tables

AnswerChoice.Label (for radio buttons)
Answer.Answer (for text)

Is there a way to ask for either the text (if text) or the answer choice label (if radio button i.e. number)?