Database Storing Question

[nlh205]

I want to use implode() to store several into a mysql database like so tags=tag1,tag2,tag3 being separated by commas. I can easily do that but i am actually wondering how i could search the tag query to find a specify tag (ex. tag2). I read that i can use mysql LIKE but when i used it, it would only find the first tag in the string. If the tag i was looking for wasn't the first it would pick it up. Any help?

[SpacePhoenix]

You'd be better off storing each tag in a separate row in the table. You'd probably need a "look-up" table as well. What's the current table structure that you've got?

Thread moved to MySQL forum

[r937]

... wondering how i could search the tag query to find a specify tag (ex. tag2).

there is a way, using the FIND_IN_SET function, but i'll tell you up front that since the design of your table violates first normal form, a search query on the tags will require a table scan, and consequently your app will not scale, i.e. the more rows you have the slower the search gets

redesign the table and add a one-to-many tags table -- this can then be indexed, and your search query will finish in fractions of a second instead of fractions of an hour

[nlh205]

redesign the table and add a one-to-many tags table -- this can then be indexed, and your search query will finish in fractions of a second instead of fractions of an hour

Would you be able to explain a little more as I only know the fair basics of mysql and i would rather use the most efficient way as you described.

[nlh205]

Do you mean to start another table called `tags` for ex with the fields `tag` to store the tag name and another field `game_id' to store the game id that the tag corresponds too?

[r937]

do me a favour and re-read post #1 -- i tried really hard but i couldn't see what kinds of things these tags are tagging -- recipes? jokes? photobombs? talking cats?

anyhow, that's the other half of the puzzle

i'll draft up a tags table for you as soon as you reveal a little more about what you're tagging

[nlh205]

It's a flash game site. So it's a tag for a game (ex. racing or word). I already have games table to store the game info in but and i was going to store the tags as i posted originally(within the games table using implode). But i want to use the most efficient way. And thanks.

[r937]

okay, so you have a table called games

presumably it's got a column called game_id

now what you want is another table like this --

CREATE TABLE game_tags
( game_id INTEGER NOT NULL
, tag VARCHAR(99) NOT NULL
, PRIMARY KEY ( game_id , tag )
, INDEX reversi ( tag, game_id )
);

to populate this table, insert one row for each tag that a particular game has...

INSERT INTO game_tags VALUES
 ( 9 , 'rpg' )
,( 9 , 'easy' )
,( 9 , 'nintendo' )
,( 37 , 'crossword' )
,( 37 , 'difficult' )

get the idea? game 9 has three tags, game 37 has two

one row per tag, as spacephoenix suggested

the primary key ensures you cannot assign the same tag to the same game more than once

the reversi index is used to make search queries based on tags efficient

[nlh205]

Thank you. That will worked and is what i figured you meant by your earlier post. You help has been great.