SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    May 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table Design and SQL query

    Hi everyone I am learning PHP and mysql using sitepoints BYO database book by Kevin Yank 4th edition.

    I've set my mind on a squash league as this is something I am interested in, understand how I want it to work and would be useful to me.

    First of all, is it okay to have a table called GAMES that is created as follows:

    Code:
    CREATE TABLE game (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    type TEXT NOT NULL, 
    matchid INT NOT NULL,
    player1id INT NOT NULL,
    player2id INT NOT NULL,
    player1score INT NOT NULL,
    player2score INT NOT NULL
    ) DEFAULT CHARACTER SET utf8;
    I think this is okay, as there is a 'many-to-two' relationship, perhaps this is just many-to-many.

    A player will play in lots of games but a game will only ever have 2 players.

    Now I am trying to obtain the id's of all of the opponents that a player may have played in the past so they can look at their head-to-head record.

    I am trying this query:

    Code:
    SELECT id FROM game
    WHERE player1_id OR player2_id != 1;
    This keeps returning all of the rows in that table yet when I use the following queries individually I can see that I get the correct results.

    Code:
    SELECT id FROM game
    WHERE player1_id != 1;
    Code:
    SELECT id FROM game
    WHERE player1_id != 2;
    I guess in plain language I am trying to 'Select all of the player ids from the games table in which player 1 took part, but exclude the player 1 id'.

    Any ideas on why this is occuring would be greatly appreciated.

    Thanks for your time and help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT matchid
         , player1id
         , player1score
         , player2id 
         , player2score
      FROM game
     WHERE 1 IN (player1id,player2id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Just the player ids?
    Code:
    SELECT DISTINCT player2_id AS opponent_id
    FROM game
    WHERE player1_id = 1
    UNION
    SELECT DISTINCT player1_id
    FROM game
    WHERE player2_id = 1
    This should give you the player ids of all opponents player 1 ever played against.

    By the way, aren't there doubles in squash?

  4. #4
    SitePoint Member
    Join Date
    May 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heh guys, thanks for both of those, that has been most helpful.

    guido 2004, I actually want the players first name from the player table too.

    Code:
    CREATE TABLE player (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    firstname TEXT NOT NULL,
    lastname TEXT NOT NULL,
    dob DATE NOT NULL,
    email TEXT NOT NULL,
    password CHAR(32)
    ) DEFAULT CHARACTER SET utf8;
    Also with regards to your comment on squash doubles, this does indeed exist although I have say I have never apart from myself on the odd occassion seen it played anywhere else but on TV, so for the moment I am not too concerned about this but it is a totally valid point.

    It would also be helpful if you could confirm whether or not the table design for 'game' is correct.

    The fact that I have match in there has been making me wonder if I need another table called match? I guess it would be something like

    Code:
    CREATE TABLE match (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    gameid INT NOT NULL
    ) DEFAULT CHARACTER SET utf8;
    Actually that wouldn't work. Both columns would need to form the primary key. I'm not sure if this new table is in fact necessary though?

    Again thanks in advance for your time and help.


Bookmarks

Posting Permissions

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