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

Question

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

Navigation

#1 by (0 votes)

1

Next, it may seem like a basic question but I need to know, I'm going straight to the point:

I have a Players registry and a game registry (tables), referenced normally. I need to know how do I enable a player to select more than one game of his or her preference, that is, in the 'game' field of the 'players' table enter more than one value, in the case how many the 'player' decides. I would like some help on how to do this in the database and if possible a starting point for doing this in php as well. I USE MYSQL + PHP in development. Thanks

php mysql relacionamento

asked by anonymous 23.01.2018 / 00:52

1 answer

(ASP.NET MVC) Create Session variable Good practices for a Model that interacts with 2 tables?

score 0 · Answer 1

You can save the game IDs by "," Here is 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 you do use 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 this case the result will be: