Seperating data in a query for correct output.

[Cep]

Hello,

I am working on a script that will query a database and list the results of games played each week.

There are three tables,

1. For storing player information (name, pokername, playerid)
2. For storing game information (gameid, starttime, endtime, date, stakes)
3. For storing results for each player in each game. (resultsid, playerid, gameid, finalposition, outhand, outbyid, gps)

I have created a function that will create a string of HTML with the results of my SQL.

However.

The problem is even though I can loop through each row and assign the data in my HTML for each players results in a game I also need to split the games up to determine a special string for the games overall header information.

If your not sure what I mean look at the code for the function where I have included IF ($I==1),

[code=php:0]
function get_lastgames() {
 
  $sql = "SELECT cw_games.game_id, cw_games.date, cw_games.starttime, cw_games.endtime, cw_games.stakes, cw_players.name, cw_players.pokername, cw_results.finalposition, cw_results.outhand, cw_results.gps, cw_players_1.name AS outby
          FROM cw_players AS cw_players_1
          RIGHT JOIN ((cw_results LEFT JOIN cw_games ON cw_results.game_id = cw_games.game_id)
          LEFT JOIN cw_players ON cw_results.player_id = cw_players.player_id)
          ON cw_players_1.player_id = cw_results.outby_id ORDER BY cw_results.finalposition";

  $result = mysql_query($sql, db()) or die("Select latest games SQL error: ".mysql_error());

  $num_rows = mysql_num_rows($result);

  $latest_results = "";

  for ($i = 1; $i <= $num_rows; $i++) {

      $row = mysql_fetch_array($result, MYSQL_ASSOC);

      if ($i==1) {
          $winnings = $row['stakes'] * $num_rows;
          $latest_results .= "<tr>
                                <td colspan='5'>
                                    Date: {$row['date']} Time: {$row['starttime']} to {$row['endtime']}
                                    Stakes: £{$row['stakes']} Total Winnings: £{$winnings}
                                </td>
                              </tr>";
      } else {
          $latest_results .= "<tr>
                                  <td>
                                      {$row['name']}
                                  </td>
                                  <td>
                                      {$row['finalposition']}
                                  </td>
                                  <td>
                                      {$row['outhand']}
                                  </td>
                                  <td>
                                      {$row['outby']}
                                  </td>
                                  <td>
                                      {$row['gps']}
                                  </td>
                            </tr>";
      }
  }
mysql_free_result($result);

return $latest_results;
}
[/code]

What I have said will pull of the special string on the first row which is fine but what about results for say game 2? They will end up running onto the end of game 1.

What could I do to determine when the row contains the next game id (and its information) during the loop so that instead of using IF($I==1) I can have game 1 special string at the top, game 1 rows appear and then game 2 special string at the top and then game 2 rows appear.

[Destruction]

Won't let me delete the post