Selecting data without specific WHERE clause

neljan

+-------+---------+-----------+---------+-----------+---------+-----------+
|  id   | player1 | p1_points | player2 | p2_points | player3 | p3_points |
+-------+---------+-----------+---------+-----------+---------+-----------+
|   1   |  John   |     1     |  Fred   |     2     |  Dave   |     3     |
+-------+---------+-----------+---------+-----------+---------+-----------+
|   2   |  Fred   |     2     |  Eric   |     3     |  Dave   |     1     |
+-------+---------+-----------+---------+-----------+---------+-----------+
|   3   |  Dave   |     3     |  Bob    |     1     |  Sam    |     2     |
+-------+---------+-----------+---------+-----------+---------+-----------+
|   4   |  John   |     1     |  Dave   |     2     |  Eric   |     3     |
+-------+---------+-----------+---------+-----------+---------+-----------+
|   5   |  Fred   |     2     |  Eric   |     3     |  John   |     1     |
+-------+---------+-----------+---------+-----------+---------+-----------+

With this db table, would it be possible to retrieve the number of games a person played and their total points, without using a WHERE clause specifying the player?

I want to display this somehow:

+---------+--------------+--------------+
| player  | games played | total points |
+---------+--------------+--------------+
|  Eric   |      3       |      9       |
+---------+--------------+--------------+
|  Dave   |      4       |      9       |
+---------+--------------+--------------+
|  Fred   |      3       |      6       |
+---------+--------------+--------------+
|  John   |      3       |      3       |
+---------+--------------+--------------+
|  Sam    |      1       |      2       |
+---------+--------------+--------------+
|  Bob    |      1       |      1       |
+---------+--------------+--------------+

The problem I have (besides the db structure =P) is that I can't use a specific WHERE clause like so...

$qry = safe_query("SELECT * FROM db_table WHERE player1='Fred' OR player2='Fred' OR player3='Fred' ");

...because there's over a hundred players and more players added all the time.

Thanks very much for your time.

laserlight

neljan wrote:
With this db table

Do you really need to have the table designed in this way? Normally, numbered columns make me wince, because they typically indicate a one to many or many to many relationship that was not normalised.

In this case, it looks like a many to many relationship: a player plays one or more games, and each game is played by many players, so something like this would be better:

Game:
+-------+---------+
|  id   |  date   |
+-------+---------+
|   1   |  ...    |
+-------+---------+
|   2   |  ...    |
+-------+---------+
|   3   |  ...    |
+-------+---------+
|   4   |  ...    |
+-------+---------+
|   5   |  ...    |
+-------+---------+

IndividualGameResult:
+-------+---------+---------+-----------+
|  id   | game_id | player  | points    |
+-------+---------+---------+-----------+
|   1   |   1     |  John   |     1     |
+-------+---------+---------+-----------+
|   2   |   1     |  Fred   |     2     |
+-------+---------+---------+-----------+
|   3   |   1     |  Dave   |     3     |
+-------+---------+---------+-----------+
|   4   |   2     |  Fred   |     2     |
+-------+---------+---------+-----------+
|   5   |   2     |  Eric   |     3     |
+-------+---------+---------+-----------+
|   6   |   2     |  Dave   |     1     |
+-------+---------+---------+-----------+
|   7   |   3     |  Dave   |     3     |
+-------+---------+---------+-----------+
|   8   |   3     |  Bob    |     1     |
+-------+---------+---------+-----------+
|   9   |   3     |  Sam    |     2     |
+-------+---------+---------+-----------+
|   10  |   4     |  John   |     1     |
+-------+---------+---------+-----------+
|   11  |   4     |  Dave   |     2     |
+-------+---------+---------+-----------+
|   12  |   4     |  Eric   |     3     |
+-------+---------+---------+-----------+
|   13  |   5     |  Fred   |     2     |
+-------+---------+---------+-----------+
|   14  |   5     |  Eric   |     3     |
+-------+---------+---------+-----------+
|   15  |   5     |  John   |     1     |
+-------+---------+---------+-----------+

You might have another column in IndividualGameResult if the player numbering is significant. Also, you might store player_id instead of player name.

neljan wrote:
would it be possible to retrieve the number of games a person played and their total points, without using a WHERE clause specifying the player?

If you take my suggestion of a different database design then it is a matter of selecting the player, the count and the sum of the points from IndividualGameResult, grouped by the player.