0

Hi I'm not sure how to even formulate this question but I'll try my best.

I have a table called games, in this table there's 3 columns, ID (INT), epoch (INT) and users (TEXT), now in each users column there is values from 4 to 25 values separated by a comma, the values are from 1 to 6 characters, so for instance: 

ID, EPOCH, USERS

1, 1461779167, (123, 58234, 548245, 225122)
2, 1461774823, (326784, 54235, 6373, 3566, 384174)
3, 1461773423, (326784, 542355, 234, 351)

Now my problem is I need to fetech only ONE among the users let's say I need to fetch the "user" 54235, it cannot be CONTAINS because it would collide with 542355 so what should I do? How should I properly arrange these columns to avoid such situations or to simplify it in general?

Barmar
  • 741,623
  • 53
  • 500
  • 612
Theiq
  • 3
  • 2
  • mySQL [find_In_set](http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set) function.. `SELECT * from table WHERE FIND_IN_SET('54235',users) >1`; Though one should really not store data in this fashion as it's not normalized. – xQbert Apr 27 '16 at 17:52
  • @xQbert Thanks, in what fashion should it be stored then? I thought of a better solution yet – Theiq Apr 27 '16 at 17:56
  • Do you really have parentheses around the list in the `USERS` column? That will prevent `FIND_IN_SET` from working. Also, they shouldn't have spaces around the commas. – Barmar Apr 27 '16 at 18:28
  • @barmar which could be mitigated by replacing the ()'s with blanks (in theory) However, what really should happen here is the data to be normalized so that all 1-M are in separate tables. – xQbert Apr 27 '16 at 18:39
  • @xQbert My assumption is that he doesn't really have the parentheses, he just used it here for grouping. – Barmar Apr 27 '16 at 18:40

2 Answers2

1

A "proper arrangement" of these columns requires some normalization.

You could have a second table, called something like "player_games", which consists of:

Columns id, game, player

Then you associate a user with a game by inserting into player_games.

So if player 2 is associated with game 5, you do:

INSERT INTO player_games (game, player) VALUES (5, 2);

And then, if you need to get all games associated with player 2:

SELECT game FROM player_games WHERE player = 2;

And if you need to get all players associated with game 5:

SELECT player FROM player_games WHERE game = 5;
Marcin N.
  • 68
  • 6
  • But that would require a lot more sql queries wouldn't it? Basically what I'm doing here is fetching a game "log", and saving all the users that participated in that particular game, if I have to make a query for each player wouldn't that be a waste of resources? – Theiq Apr 27 '16 at 18:00
  • Thankfully SQL databases are robust and optimized for these kinds of situations :) With proper indexing you really shouldn't run into performance issues. I'm pretty sure that examining your existing TEXT column is much, much slower than querying a separate, normalized table. – Marcin N. Apr 27 '16 at 18:01
0

simplest hack you can do is instead of matching "54235" you can match "54235,". match comma as well and it will do the trick. make sure you columns comma arrangement is proper atleast.

but this is not the right way to store results. you should have different table for mapping.

kanchan
  • 339
  • 1
  • 3
  • 15