Mysql - Create field to insert more than one value from another table

Question

Next, it may seem like a basic question but I need to know, I’ll get right to the point:

I have a register of Players and a register of games (tables), usually referenced. I need to know how to do so that a player can select more than one game of his own, that is, in the 'game' field of the 'players' table insert more than one value, in case how many the 'player' decides. I would like a help on how to do this in the database and if possible a starting point to do this in php as well. UTILIZO MYSQL + PHP in development. Thank you

Answer 1

From what you described it becomes a relation from N to N, as a player may prefer one more game as well as a game may be preferred by more than one player, in which case you must create another table with the player id and game id.

Answer 2

You can save the game ID’s separated by "," here’s an example:

CREATE TABLE jogadores(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(50),
    jogo VARCHAR(255)
);

CREATE TABLE jogos(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    descricao VARCHAR(255)
);


INSERT INTO jogos(descricao)VALUES('Jogo 1'),('Jogo 2'),('Jogo3'),('Jogo 4'),('Jogo 5');
INSERT INTO jogadores(nome, jogo)values('Jogador Teste', '1,3,5');

And when I make you use the JOIN use the native function of MySQL FIND_IN_SET

SELECT
    *
FROM jogadores j
INNER JOIN jogos jo
ON FIND_IN_SET(jo.id,j.jogo);

In that case the result will be: