Everybody! This is important. In a few days, these forums will be moving over to using the totally sweet Discourse platform. To ensure this migration happens smoothly with no loss of content, these forums are currently in a read-only mode. I do apologize for the inconvenience.

There is never a good time to turn the forums off for an extended period of time, but I promise the new forums will be a billion times better. I'm pretty sure of it.

See you all on the other side in a few days, and if you have any (non-technical) questions, please e-mail me at kirupa@kirupa.com. For technical questions, try to find a tutorial that corresponds to what you are looking for and post in the comments section of that page.

Cheers,
Kirupa

Results 1 to 9 of 9

Thread: Tag based searching in MySQL.

  1. #1

    Tag based searching in MySQL.

    So, I'm working on a web-app that uses tag-based searching of items in a database (well, it does more than that, but for the purpose of this post, that's what it does).

    Say I've got a table row with the following column and row values:
    1. id:1
    2. photo: neato_picture.jpg
    3. tags: Cool, Awesome, Friends, Funny
    1. id: 2
    2. photo: blowfish.jpg
    3. tags: Aqua, Fish, Funny, Ocean Cool
    Anyhow, you get the idea. Say I have those two rows in my DB (the rows' contents are irrelevant)

    What does the query look like to look through the `tags` column and return all rows that have the tag 'Cool' associated with them.

    Thanks in advance guys! Any help is appreciated.
    "Give me the place to stand, and I shall move the earth" - Archimedes
    "The whole problem with the world is that fools and fanatics are always so certain of themselves, but wiser people so full of doubts." - Bertrand Russell

  2. #2
    Well if it's not too much of a hassle, may I recommend creating a tags table and giving it columns id, pid, and tag so that you can do a straight select.

    Else, SELECT * FROM `table_name` WHERE `tags` LIKE 'Cool'
    got pwnt?

  3. #3
    Unfortunately the LIKE method doesn't work as well as I thought it would (first thing I tried).

    Code:
    SELECT * FROM `table_name` WHERE `tags` LIKE 'Cool'
    Doesn't work at all.

    Code:
    SELECT * FROM `table_name` WHERE `tags` LIKE '%Cool%'
    Works KINDA, but it also matches "Coo" or "ool" or even "oo". I'm looking to match specific words.

    The extra table may be a last resort, but if anybody has a method that will allow me to localize the tags in the current table, I'd love it.
    Last edited by lorren.biffin; December 28th, 2007 at 12:30 AM.
    "Give me the place to stand, and I shall move the earth" - Archimedes
    "The whole problem with the world is that fools and fanatics are always so certain of themselves, but wiser people so full of doubts." - Bertrand Russell

  4. #4
    ^ Read the MySQL documentation for "LIKE". I'm fairly sure they have something in there about doing what you're doing.
    COLOURlovers | Member #2 of the kirupa XBox 360 Club
    "I think most people would agree that your computer breaking is a Christmas miracle." ~shane-c to Timmytots

  5. #5
    Have a tags table, the way you have it now will cause alot of redundant data.
    Member #2 of the "I wont critique Timmytot's designs anymore" club.

  6. #6
    Quote Originally Posted by Esherido View Post
    ^ Read the MySQL documentation for "LIKE". I'm fairly sure they have something in there about doing what you're doing.
    I've not found anything on the web or in either of my MySQL books dealing with tag based searching using LIKE...unless I'm missing something

    Quote Originally Posted by Seb Hughes View Post
    Have a tags table, the way you have it now will cause alot of redundant data.
    It's looking like that's where I'll be heading...mind if I ask what kind of redundant data you see coming from the current situation?
    "Give me the place to stand, and I shall move the earth" - Archimedes
    "The whole problem with the world is that fools and fanatics are always so certain of themselves, but wiser people so full of doubts." - Bertrand Russell

  7. Hmmm...well, this seperate tags table idea, as great as it is in theory, is turning into much more of a hassle than it really ought to be.

    Please tell me somebody has a better solution that will allow me to keep the tags on the same table?
    "Give me the place to stand, and I shall move the earth" - Archimedes
    "The whole problem with the world is that fools and fanatics are always so certain of themselves, but wiser people so full of doubts." - Bertrand Russell

  8. #8
    602
    posts
    Backend Specialist
    Another way is to retrieve the tags entry (Cool, Awesome, Friends, Funny), and then use php to parse the results using
    PHP Code:
    explode(","$rs['tags']) 
    and search the array for a match. This is probably the worst way to do it depending on the size of the table.

    Or, you can make sure there is a comma before and after each tag, or some other identifier. So, if you store the tags as (,Cool,Awesome,Friends,Funny,) with commas before and after, the MySQL query of "LIKE %,Cool,%" will return accurate results. Then use php to clean it up before displaying.

    Obviously, you should be using a tags table, but if that's too much work to implement since the project is already underway, I'd try one of those. hth.

  9. #9
    No way should you be having each product store the tags, your database will seriously get out of hand... just to let you know.

    But if you are dead set on one table, then you could use Full Body Text search, not sure what the MySQL syntax is.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012