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:
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:
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.
SELECT id FROM game
WHERE player1_id != 1;
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.