-
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
-
have you tried SELECT * FROM `blog_posts` WHERE '0' IN (`post_sections`) ?
-
that gives me an empty result set
-
We've got it backwards:
PHP Code:
// CORRECT
SELECT * FROM `blog_posts` WHERE `post_sections` IN ('0')
// INCORRECT
SELECT * FROM `blog_posts` WHERE 0 IN (`post_sections`)
however, I think what you actually want is a LIKE statement:
PHP Code:
SELECT * FROM `blog_posts` WHERE `post_sections` LIKE '%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_posts` WHERE `post_sections` LIKE '%,0,%'
and get only rows with " 0 "
-
-
glad I could help :) I think you replied before I finished my edit above. You may want to check it out.
-
yeah, I did, but I noticed it after i posted
