SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist webchalkboard's Avatar
    Join Date
    Jan 2005
    Location
    Bristol, UK
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Tricky select statement

    Hello,

    I need some help.

    I have a table in my database with a column in which contains comma delimited values.

    Originally I only had the one value in there, but I wanted to expand this to allow multiple categories so I made the value comma delimited.

    This is easy to work with if I select the value out and split up in PHP, but I want to do a direct select based on each of the individual values. I.e. my database looks like this:

    +--------+-------+---------+------+------------+------------+
    | name | owner | categories | sex | birth | death |
    +--------+-------+---------+------+------------+------------+
    | Bowser | Diane | 1,2,4,6,85 | m | 1989-08-31 | 1995-07-29 |
    +--------+-------+---------+------+------------+------------+

    And I want to do a select like:

    select * from tablename where categories='2'

    Any suggestions? I am being lazy really I know I should have this in another table... but don't want to change all the other code i've done.

    Thanks,
    Tom
    Websites for Sale - Sell websites in a purpose built marketplace
    Then do some Shopping

  2. #2
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmm
    Code:
    SELECT *
    FROM tablename
    WHERE categories LIKE '%,2,%'
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  3. #3
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    Finland
    Posts
    703
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You might be able to do it like this:
    Code:
    SELECT * FROM t WHERE FIND_IN_SET(2,categories) > 0

  4. #4
    SitePoint Evangelist webchalkboard's Avatar
    Join Date
    Jan 2005
    Location
    Bristol, UK
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sorccu
    You might be able to do it like this:
    Code:
    SELECT * FROM t WHERE FIND_IN_SET(2,categories) > 0
    Thanks thats an interesting bit of code, will look into that.

    I have stopped being lazy though and in the time and put that data in another table and got a nice one to many relationship now, I guess that's the point of relational databases after all eh.
    Websites for Sale - Sell websites in a purpose built marketplace
    Then do some Shopping

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by webchalkboard
    Thanks thats an interesting bit of code, will look into that.
    don't look to hard because it's not standard SQL.
    I have stopped being lazy though and in the time and put that data in another table and got a nice one to many relationship now, I guess that's the point of relational databases after all eh.
    yes it is. and that's also standard SQL, so keep doing it that way!

  6. #6
    SitePoint Evangelist webchalkboard's Avatar
    Join Date
    Jan 2005
    Location
    Bristol, UK
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    don't look to hard because it's not standard SQL.
    Might not be standard but it does work, I just used it for something else. I guess its just a mysql thing... well can't see us changing databases so should be ok
    Websites for Sale - Sell websites in a purpose built marketplace
    Then do some Shopping

  7. #7
    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)
    Quote Originally Posted by webchalkboard
    I just used it for something else.
    stop that!!

    that function requires a table scan

    if you are interested in performance, you'll normalize your tables, so that you do not store a comma-delimited list of values in a single column
    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
  •