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?

Improve this question
5
  • mySQL 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. Commented Apr 27, 2016 at 17:52
  • @xQbert Thanks, in what fashion should it be stored then? I thought of a better solution yet Commented Apr 27, 2016 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. Commented Apr 27, 2016 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. Commented Apr 27, 2016 at 18:39
  • @xQbert My assumption is that he doesn't really have the parentheses, he just used it here for grouping. Commented Apr 27, 2016 at 18:40

2 Answers 2

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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?
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.
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.

Improve this answer

4 Comments

then for ex. 17195, would still match 117195,
than search for ", 17195,"
@kanchan That won't work if 17195 is the first or last item in the list.
than u need to search for the column thrice. i mean in where condition your columnname LIKE "%, 17195,%". than columnname LIKE "%17195," and finally columnname LIKE "17195,%".

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.