Page 1 of 1

Extracting 'group by' query results in a table

Posted: Thu Feb 23, 2006 12:40 am
by ehmer
feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


Hi all

I currently have a query that looks something like this:

Code: Select all

SELECT tips.username, fixtures.round,
SUM(CASE
WHEN tips.tip='draw' AND fixtures.result='draw' then 1
WHEN tips.tip=fixtures.result then 1
else 0 end) points
FROM fixtures
JOIN tips on (fixtures.round=tips.round AND fixtures.game=tips.game)
GROUP BY tips.username, fixtures.round
LIMIT 0,50
The data currently returned is:

username round number points

Code: Select all

Angela 10 8
Angela 11 2
ehmer 10 5
ehmer 11 7
There will eventually be numerous rows of data returned for each username. ie for each user there will be up to 26 rounds and 26 points that are relevant.

I need to reference username in a table once only with the related 'round'
and 'points' data in the same row and then total the points for each username. eg

Code: Select all

username round10 round11 round12 round13 total
Angela 8 2 -- -- 10
ehmer 5 7 -- -- 12

I'm currently using mysql_fetch_assoc() like this.

Code: Select all

while ($row = mysql_fetch_assoc($result)) {
echo $row["username"];
echo $row["round"];
echo $row["points"];
}
I'm not sure how to get the data in the correct row/column and only displaying 'username' once.

Would a foreach loop help with this? If so how would this work?

Really appreciate any suggestions
David


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Thu Feb 23, 2006 2:26 am
by s.dot
I think something like this should suffice:

Code: Select all

$points = array();
if($result){
   while($row = mysql_fetch_assoc($result)){
      $points[$row['username']] = $row['points'];
      $pointTotal = "";
      if(count($points)){
         foreach($points AS $point){
            $pointTotal += $point;
         }
      }
   }

   mysql_data_seek($result,0);

   while($row = mysql_fetch_assoc($result){

      echo "<tr><td>Username: {$row['username']}</td>
      <td>Points Per Round: ";
      if(count($points)){
      foreach($points[$row['username']] AS $user => $perRound){
         echo $perRound.'<br />';
      } ELSE {
         echo "No Data";
     }
      echo "</td>
      <td>";
      if(!empty($total)){
         echo $total;
      } ELSE {
         echo "No Data";
      }
      echo "</td></tr>";
   }
}
[[im sure there's a few syntax errors =[]]

Actually I think my idea is right but my code is way off... =[ again

Posted: Thu Feb 23, 2006 3:30 am
by ehmer
Thanks for the reply. You're approach may be right but I'm not experienced enough to be confident about the syntax. I've tried the code you posted, but it didn't work out too well. Not sure why, could be syntax.

Any other suggestions?

Thanks
David

Posted: Thu Feb 23, 2006 7:04 am
by ehmer
Have got this sorted out now. A suggestion from a post at Sitepoint helped. Thanks

Posted: Thu Feb 23, 2006 8:08 pm
by ehmer
Just one final issue on this. I need to sort the table by 'order total'. Given that the 'total' is calculated in PHP rather than being available as a database field, I can't use the 'order by' option in my query.

Is it possible to order the rows using PHP?

Posted: Thu Feb 23, 2006 8:18 pm
by josh
Either use an array sorting function like

sort() or asort()

or have mysql calculate the total for you using an agregate function such as SUM()


an example of the mysql method (my recommended way to do it)

Code: Select all

SELECT SUM(`price`) as `total` from `orders` GROUP BY `customer` ORDER BY `total` DESC