SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    GROUP_CONCAT LIKE 2 NOT LIKE 20 etc?

    Ok...

    I have used group_concat to consolidate all the 'genreids' as follows in a field...

    2, 3, 4

    How would i perform a search of this field, so that if i wanted to find genreid 2? But not 20, 21, etc?

    At current ive been using:

    ....AND genreids LIKE '%2%'

    However the above obviously matches too much :/

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your example shows spaces after the commas

    true?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by imxuk View Post
    Ok...

    I have used group_concat to consolidate all the 'genreids' as follows in a field...

    2, 3, 4
    so you had normalized data before and now you've decided to denormalize it? why?

  4. #4
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, concat is set up as follows:

    Code:
    GROUP_CONCAT(genre.genrename ORDER BY genre.genrename SEPARATOR ', ')
    If only the concat field added a comma after its last value in the list it would be easy

    Hmmm perhaps if i remove the space in the separator...

  5. #5
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm comma at the end wouldnt make any difference.... back to the drawing board

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hey, i got yer drawing board right here

    guelphdad, there are two GROUP_CONCATs

    that was done with my help, because it's the only sane way to return two one-to-many relationships at the same time

    imsux, could you remove the space in the GROUP_CONCAT, please

    i'm curious, why do you want to search in the concatenated field? why aren't you doing the searching on the database side?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably out of pure laziness, so i can simply modify the current query. I guess i could put in if, else in the PHP code to determine which query should be used...

    At current i have the sections of the query broken up into separate variables.. then for the $searchsqlwhere = 'WHERE 1=1 ';...

    Im simply passing/appending extras to it..

    $searchsqlwhere .='AND genreids LIKE.....';

  8. #8
    SitePoint Enthusiast imxuk's Avatar
    Join Date
    Dec 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe i should do my search based on the 'genres' as opposed to genreids.... this would get around it

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    where were you planning on doing this search?

    i mena, in which query? could we see it?
    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
  •