Selecting data without specific WHERE clause
Results 1 to 2 of 2

Thread: Selecting data without specific WHERE clause

  1. #1
    Senior Member
    Join Date
    Jul 2010
    Location
    London
    Posts
    113

    Selecting data without specific WHERE clause

    Hi

    Code:
    +-------+---------+-----------+---------+-----------+---------+-----------+
    |  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:

    Code:
    +---------+--------------+--------------+
    | 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...

    PHP Code:
    $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.

  2. #2
    PHP Witch laserlight's Avatar
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    13,561
    Quote Originally Posted by neljan
    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:

    Code:
    Game:
    +-------+---------+
    |  id   |  date   |
    +-------+---------+
    |   1   |  ...    |
    +-------+---------+
    |   2   |  ...    |
    +-------+---------+
    |   3   |  ...    |
    +-------+---------+
    |   4   |  ...    |
    +-------+---------+
    |   5   |  ...    |
    +-------+---------+
    Code:
    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.

    Quote Originally Posted by neljan
    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.
    Use Bazaar for your version control system
    Read the PHP Spellbook
    Learn How To Ask Questions The Smart Way

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •