Data Storage - game results

[eluser]Michael Wales[/eluser]
I would create the following table and only insert completed games. There's no need to insert games that have yet to be completed (you can place the "X" on a FALSE result set).

games
-----
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
player1 INT NOT NULL
player2 INT NOT NULL
score INT NOT NULL

To get all of the games and put into a multidimensional array so we can make our table:

Code:

$game_results = array();

$query = $this->db->get('games');
if ($query->num_rows() > 0) {
  $row = $query->row();
  $game_results[$row->player1][$row->player2] = $row->score;
  // Do the inverse, since there is no home/away
  $game_results[$row->player2][$row->player1] = $row->score;
}

Now let's create our pretty little table (not sure the looping through the array is correct in this example (no environment to test in atm), but you get the concept):

Code:

<table>
&lt;?php foreach ($game_results as $p1 => $result): ?&gt;
  <tr>
  &lt;?php foreach ($result as $p2 => $score): ?&gt;
    <td>&lt;?php echo $score; ?&gt;</td>
  &lt;?php endforeach; ?&gt;
  </tr>
&lt;?php endforeach; ?&gt;

The only concern would be on insertion - make sure you don't have the same game listed somewhere twice.