Page 1 of 1

Getting Posts Based on Multiple Tags

Posted: Tue Sep 22, 2009 1:07 am
by HiddenS3crets
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

Code: Select all

post_id     tag
-------     -------------
1           php
1           programming
1           code
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

Code: Select all

post_id     tag
-------     -------------
1           php
1           programming
1           code
2           programming
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?

Re: Getting Posts Based on Multiple Tags

Posted: Tue Sep 22, 2009 3:36 pm
by VladSun
Get some ideas from this thread:
viewtopic.php?f=2&t=87657

Re: Getting Posts Based on Multiple Tags

Posted: Tue Sep 22, 2009 6:50 pm
by HiddenS3crets
ok I checked out the solution on that post above and read about the IN tag, which I then used to modify my SQL query

Code: Select all

SELECT * FROM `tags` WHERE `tag` IN ('php', 'programming')
The problem is that IN returns any row where one of the values matches (php or programming). What I need is for it to only returns rows where there is a match for one post_id and both tags. I'm researching currently but so far no luck

Re: Getting Posts Based on Multiple Tags

Posted: Tue Sep 22, 2009 8:23 pm
by HiddenS3crets
I created a solution :)

Code: Select all

SELECT posts.* FROM `posts` LEFT JOIN `tags` ON tags.post_id = posts.post_id WHERE tags.tag IN ($tags_str) GROUP BY tags.post_id HAVING COUNT(tags.post_id ) = count($tags_array)
$tags_array has a value for each tag. $tag_str is structured to work properly with the IN clause. By stating COUNT(tags.post_id) = count($tags_array) I am able to only retrieve the rows where the each tag is matched for a given post_id (the count($tags_array) is the php function, not SQL)

Thanks for the help!

Re: Getting Posts Based on Multiple Tags

Posted: Wed Sep 23, 2009 3:11 am
by VladSun
Glad to hear you did it :)

I would suggest you to normalize your DB design, so that it has the same structure as the one in the thread I mentioned.