Grouping 2

[craygo]

I am having a problem grouping by 2 catagories then giving a total after the second group only.  here is the query and the code.
[code]<?php
$matches = "SELECT $mtable.week, t1.team_name AS team1, t2.team_name AS team2, p1.username AS pl1, p2.username AS pl2, p1.rating AS pl1r, p2.rating AS pl2r, $rtable.p1_color AS color, $rtable.m_id AS matchid
FROM (((($mtable JOIN $rtable ON $mtable.m_id = $rtable.m_id)
LEFT JOIN $utable AS p1 ON $rtable.p1_id = p1.user_id)
LEFT JOIN $utable AS p2 ON $rtable.p2_id = p2.user_id)
LEFT JOIN $ttable AS t1 ON $rtable.t1 = t1.team_id)
LEFT JOIN $ttable AS t2 ON $rtable.t2 = t2.team_id
WHERE $mtable.s_id = '$sid'
ORDER BY $mtable.week, $rtable.m_id, p1.rating DESC";
$mres = mysql_query($matches) or die (mysql_error());
$lastweek = '';
$match = '';
$t1tot = 0;
$t2tot = 0;
$i=0;
while($mr = mysql_fetch_assoc($mres)){
  if($mr['week'] != $lastweek){
          echo "<tr>
        <td colspan=5 class=style24 id=$i>Week".$mr['week']."</td>
        </tr>\n";
  }
      if($mr['matchid'] != $match){
        if ($match != '') {
        echo "<tr>
            <td width=20>&nbsp;</td>
            <td colspan=4>Total: $t1tot vs $t2tot</td>
            </tr>\n";
            $t1tot = 0;
            $t2tot = 0;
        }
      echo "<tr>
            <td width=20><br /></td>
            <td colspan=4 class=style16>".$mr['team1']." vs ".$mr['team2']."</td>\n
            </tr>";
      }

echo "<tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td width=150>".$mr['pl1']."</td>
      <td width=50>vs</td>
      <td width=150>".$mr['pl2']."</td>
      </tr>\n";
$lastweek = $mr['week'];
$match = $mr['matchid'];
$t1tot += point_val($mr['pl1r']);
$t2tot += point_val($mr['pl2r']);
$i++;
}
      echo "<tr>
            <td>&nbsp;</td>
            <td colspan=4>Total: $t1tot vs $t2tot</td>\n
            </tr>\n";
?>[/code]

And this is what I get
[img]http://www.theelders.net/misc/chesspage.jpg[/img]

As you can see it is putting one of totals below the second week.

Thanks in advance

Ray

[Psycho]

Try this (not tested):
[code]<?php
$matches = "SELECT $mtable.week, t1.team_name AS team1, t2.team_name AS team2, p1.username AS pl1, p2.username AS pl2, p1.rating AS pl1r, p2.rating AS pl2r, $rtable.p1_color AS color, $rtable.m_id AS matchid
FROM (((($mtable JOIN $rtable ON $mtable.m_id = $rtable.m_id)
LEFT JOIN $utable AS p1 ON $rtable.p1_id = p1.user_id)
LEFT JOIN $utable AS p2 ON $rtable.p2_id = p2.user_id)
LEFT JOIN $ttable AS t1 ON $rtable.t1 = t1.team_id)
LEFT JOIN $ttable AS t2 ON $rtable.t2 = t2.team_id
WHERE $mtable.s_id = '$sid'
ORDER BY $mtable.week, $rtable.m_id, p1.rating DESC";
$mres = mysql_query($matches) or die (mysql_error());

$currentWeek = '';
$currentMatchID = '';
$t1tot = 0;
$t2tot = 0;
$i=0;

while($mr = mysql_fetch_assoc($mres)){

  //Show prev match totals
  if($mr['matchid'] != $currentMatchID && $match != '') {
            echo "<tr>
              <td width=20>&nbsp;</td>
              <td colspan=4>Total: $t1tot vs $t2tot</td>
              </tr>\n";
              $t1tot = 0;
              $t2tot = 0;
  }

  //Show week title
  if($mr['week'] != $currentWeek){
      echo "<tr>
            <td colspan=5 class=style24 id=$i>Week".$mr['week']."</td>
            </tr>\n";
      $currentweek = $mr['week'];
      $i++;
  }

  //Show match title
  if($mr['matchid'] != $currentMatchID){
      echo "<tr>
            <td width=20><br /></td>
            <td colspan=4 class=style16>".$mr['team1']." vs ".$mr['team2']."</td>\n
            </tr>";
      $currentMatchID = $mr['matchid'];
  }

  //Show individual games
  echo "<tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td width=150>".$mr['pl1']."</td>
        <td width=50>vs</td>
        <td width=150>".$mr['pl2']."</td>
        </tr>\n";

  //Add totals
  $t1tot += point_val($mr['pl1r']);
  $t2tot += point_val($mr['pl2r']);

}
  //Show last match total
  echo "<tr>
        <td>&nbsp;</td>
        <td colspan=4>Total: $t1tot vs $t2tot</td>
        </tr>/n";
?>[/code]

[craygo]

Thanks for response, but no good.

Still trying to see what's going on!!

[Psycho]

Ok, what happens when you use that code? Are you getting any errors? What problems are you getting with the output?

Edit, i had a typo in the above: $currentWeek did not have the same case where used.

[craygo]

Nope I tried fixing the case error and nothing. It is only putting the total on the bottom.

[craygo]

Figured it out. Had to put an extra check after the week change to look for the match change.

Anyone looking to group 2 catagories and total up one here is the final code I used

[code]<?php
$mres = mysql_query($matches) or die (mysql_error());
$lastweek = '';
$lastmatch = '';
$t1tot = 0;
$t2tot = 0;
$i=0;
while($mr = mysql_fetch_assoc($mres)){
    if($mr['week'] != $lastweek){
      if($lastmatch != ''){
      // Display the match Total
      echo "<tr>
            <td width=20>&nbsp;</td>
            <td colspan=4 class=totals>Total: $t1tot vs $t2tot</td>
            </tr>\n";
            $t1tot = 0;
            $t2tot = 0;
      }
    // Display The week numbers
    echo "<tr>
        <td colspan=5 class=style24 id=$i>Week".$mr['week']."</td>
        </tr>\n";
        $i++;
    }
    if($mr['matchid'] != $lastmatch){
      if($lastmatch != '' && $mr['week'] == $lastweek){
      // Display the match Total
      echo "<tr>
            <td width=20>&nbsp;</td>
            <td colspan=4 class=totals>Total: $t1tot vs $t2tot</td>
            </tr>\n";
            $t1tot = 0;
            $t2tot = 0;
      }
    // Display the match teams
    echo "<tr>
          <td width=20><br /></td>
          <td colspan=4 class=style16>".$mr['team1']." vs ".$mr['team2']."</td>
          </tr>\n";
    }
// Show players in the match
echo "<tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td width=150>(".$mr['color'].") ".$mr['pl1']." -- (".point_val($mr['pl1r']).")</td>
      <td width=50>vs</td>
      <td width=150>".$mr['pl2']." -- (".point_val($mr['pl2r']).")</td>
      </tr>\n";
// Reset points and weeks
$lastweek = $mr['week'];
$lastmatch = $mr['matchid'];
$t1tot += point_val($mr['pl1r']);
$t2tot += point_val($mr['pl2r']);
}
echo "<tr>
      <td>&nbsp;</td>
      <td colspan=4 class=totals>Total: $t1tot vs $t2tot</td>
      </tr>\n";
?>[/code]

Ray

[Psycho]

One critique. You have two sections at the top for writing the match totals. You could simply make a switch for the match total as the first section in the loop and to show the match total if you are starting data for a new match AND if the last match !="".

I noticed another error uin my previous script as well - I should have taken the time to create some test data so I could actually run it. Anyway, you have something that works now.