Results 1 to 7 of 7

Thread: Select IN table

  1. #1
    Join Date
    Jun 2007
    Posts
    543
    Thanks
    3
    Thanked 78 Times in 78 Posts
    Blog Entries
    1

    Default Select IN table

    I am setting up a blog system, and am having trouble setting up categories. I have a table, blog_posts, which stores information about the post. There is a column, post_sections, which stores an imploded array of the sections' ids eg: 0,1,2,3,4. Now if I want to select posts by section 0, I try SELECT * FROM `blog_posts` WHERE 0 IN (`post_sections`), but this only works if the first of the array is 0 eg: 0,1,2,3 but not if it is somewhere else eg 1,2,4,0,7. Is there anyway I can make this work?
    Thanks
    [Jasme Library (Javascript Motion Effects)] My Site
    /\/\@ /\/\@|{
    There are 10 kinds of people in the world, those that understand binary and those that don't.

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    have you tried SELECT * FROM `blog_posts` WHERE '0' IN (`post_sections`) ?

  3. #3
    Join Date
    Jun 2007
    Posts
    543
    Thanks
    3
    Thanked 78 Times in 78 Posts
    Blog Entries
    1

    Default

    that gives me an empty result set
    [Jasme Library (Javascript Motion Effects)] My Site
    /\/\@ /\/\@|{
    There are 10 kinds of people in the world, those that understand binary and those that don't.

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    We've got it backwards:
    PHP Code:
    //  CORRECT
    SELECT FROM `blog_postsWHERE `post_sectionsIN ('0')

    //  INCORRECT
    SELECT FROM `blog_postsWHERE 0 IN (`post_sections`) 
    however, I think what you actually want is a LIKE statement:
    PHP Code:
    SELECT FROM `blog_postsWHERE `post_sectionsLIKE '%0%' 
    (see example here)

    Edit:
    of course, that will return " 10 " and " 105 " as well as " 0 ".
    As you can see in my example, '%h%' returns all the "home" rows but also an extra one - that row has a "parent" value of "archive".
    maybe redesign your post_sections fields to start and end with a comma as well, like so: ,0,1,2,3,4,
    then you could write your statement like this:
    PHP Code:
    SELECT FROM `blog_postsWHERE `post_sectionsLIKE '%,0,%' 
    and get only rows with " 0 "
    Last edited by traq; 10-25-2009 at 06:28 PM.

  5. #5
    Join Date
    Jun 2007
    Posts
    543
    Thanks
    3
    Thanked 78 Times in 78 Posts
    Blog Entries
    1

    Default

    wow thanks, it works
    [Jasme Library (Javascript Motion Effects)] My Site
    /\/\@ /\/\@|{
    There are 10 kinds of people in the world, those that understand binary and those that don't.

  6. #6
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    glad I could help I think you replied before I finished my edit above. You may want to check it out.

  7. #7
    Join Date
    Jun 2007
    Posts
    543
    Thanks
    3
    Thanked 78 Times in 78 Posts
    Blog Entries
    1

    Default

    yeah, I did, but I noticed it after i posted
    [Jasme Library (Javascript Motion Effects)] My Site
    /\/\@ /\/\@|{
    There are 10 kinds of people in the world, those that understand binary and those that don't.

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
  •