SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Storing Question

    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?

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nlh205 View Post
    ... 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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 --
    Code:
    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...
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you. That will worked and is what i figured you meant by your earlier post. You help has been great.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •