Getting Posts Based on Multiple Tags
Posted: Tue Sep 22, 2009 1:07 am
I have a tagging system set up for my posts; the tags table is structured to hold the post_id, plus one tag per row.
Example: I submit a post (post_id 1) and give it the tags php, programming, code. the tags table will look like
Now I want to be able to retrieve the post_id of a post based on it matching the criteria of multiple tags.
Let's say the tags table looks like this
If I search for posts with the tag 'programming' I would get back post_id 1 and 2
My problem arises when I want to specify multiple tags to search. I want to be able to say "search for tags programming and php" and the expected result would be post_id 1 because it's the only post that has the tags 'programming' and 'php'
How would I structure an SQL statement to accomplish this?
Example: I submit a post (post_id 1) and give it the tags php, programming, code. the tags table will look like
Code: Select all
post_id tag
------- -------------
1 php
1 programming
1 codeLet's say the tags table looks like this
Code: Select all
post_id tag
------- -------------
1 php
1 programming
1 code
2 programmingMy problem arises when I want to specify multiple tags to search. I want to be able to say "search for tags programming and php" and the expected result would be post_id 1 because it's the only post that has the tags 'programming' and 'php'
How would I structure an SQL statement to accomplish this?