SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    Body
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with select statement

    I have an image gallery that I want to add very basic tagging capability to. I've added the tags as rows in mysql columns. The person uploading an image can input 4 tags for their images. So they might tag images like this

    Format: tag1 | tag2 | tag 3 | tag4

    Example:

    Image 1: tiger | india | boat |flight
    Image 2: duck | waterfowl | sunset | zoo
    Image 3: india | tiger | hotel | dusk

    If someone wants to search on pictures of tigers, then I want images 1 & 3 to show up. But the problem is that because tiger is stored in column 'tag1' in picture 1, and column 'tag3' in image I can't figure out a select statement that searchs all 4 columns and returns a result from any of the column if a match is found.

    Thanks!

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    Body
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind.. must be too late... heh.. just changed my "and" to an "or" and it worked.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your query will likely require a table scan

    not a serious problem, unless your table gets significantly big

    you may want to take this opportunity during early development to change the table structure to a separate one-to-many structure, so that the tags can be properly indexed and query retrieval optimized so that the number of image entries does not affect retrieval time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •