group by help....

[mfacer]

hi... I have the following database structure:

activityID int(11)
answer varchar(2)
processID (varchar(10)

the following is an example of the data in the table above.....

ACTIVITYID  | PROCESSID	| ANSWER
=================================
1.01.01	    | 1.01  	| SS
1.01.01	    | 1.01	| BU
1.01.02	    | 1.02	| BU
1.01.03	    | 1.03	| OT

I need to out put the above based on the activity ID... I have used the following SQL statement

"SELECT activityID,processID,answer FROM resourceAnswers GROUP BY activityID ORDER BY activityID ASC"

I am then using PHP to format that in to a table which would look something like

ACTIVITYID  |  PROCESSID  |  AA  SS  BU  OT
==============================================
1.01.01	    |     1.01    |       1   1
1.01.02     |     1.02    |           1
1.01.03     |     1.03    |               1

so the "1"'s are there to represent the answers..... but my PHP code isnt working properly.... am I doing something wrong with the SQL??

here's the PHP stripped down

while ($row = mysql_fetch_array($result) {

   echo $row['activityID'] . " - ";
   echo $row['processID'] . " - ";
   echo $row['answer'] . " ";
   echo $row['answer'] . " ";
   echo $row['answer'] . " ";
   echo $row['answer'] . " ";

}

I am guessing I need to do something with the answer part - should I have grouped by answer or something?

thanks for any help.....

[eoghain]

You will need to do more work than that to get your answers into the format that you want for output. Something like this would work:

$convertedData = array()
$currID = ""
$rowID = -1; // will end up with 0 based array.
while ($row = mysql_fetch_array($result) {
   if ($currID != $row["activityID"])
   {
      $currID = $row["activityID"];
      $rowID++;
      $convertedData[$rowID]["activityID"] = $row["activityID"];
      $convertedData[$rowID]["processID"] = $row["processID"];
   }

   $convertedData[$rowID][$row["answer"]]++; 
}

foreach ($row in $convertedData)
{
   echo $row['actvityID'] . " - ";
   echo $row['processID'] . " - ";
   echo $row['AA'] . " ";
   echo $row['SS'] . " ";
   echo $row['BU'] . " ";
   echo $row['OT'] . " "; 
}