querying a database based on a dynamic amount of conditions

Question

I am building a 'tag' system. I quoted it because its not quite a tag system, but close enough.

Obviously, when dealing with tags, the user may very well use multiple tags and expect to filter the results to an even greater depth.

The problem I am coming across is how exactly to do this.

there are 2 ways I can think of doing it so far, but I would be greatly appreciative if someone out there can explain the correct way.

way 1) Do an individual query for each tag then sort/combine/play with the data afterward. way 2)

SELECT i2t.item_id
FROM Item2Tag i2t
INNER JOIN Tag t
ON i2t.tag_id = t.tag_id
WHERE t.tag_text IN ('beach','cloud','waves')
GROUP BY i2t.item_id

The above is my query. This is based on a modified version of Toxi's solution. Hopefully you see whats going on...essentially there are 3 tables, 1 for info, 1 for tags, and another to connect them.

This is currently what I am doing: I substitute 'beach','cloud','waves' with a variable. Then, within the PHP code, I convert the array to a string with each word separated by comma's and simply insert it into the query. so something like :

foreach ($tagarray as $tag){
$string = .$tag1.", " }

That is of course a bit over simplified, since I would need to correctly end the last tag without a comma..but hopefully you get the point.

so, $string becomes equal to 'tag1, tag2, tag3'

SELECT i2t.item_id
FROM Item2Tag i2t
INNER JOIN Tag t
ON i2t.tag_id = t.tag_id
WHERE t.tag_text IN ('$string')
GROUP BY i2t.item_id

Is there a better way to do this?

Answer 1

The database structure is good with those 3 tables. But there's a little problem with your query. Not a syntax one thou. Let's take an example. users search for content with tags 'a' and 'b'. If you have a content with only tag 'a' it will turn up as a result. Also contents with only 'b' tags. You should append to query something like:

HAVING COUNT(t.tag_id) = x

, where x is the length of the numbers of tags you are querying with (in my example 2, in yours 3). This way a content will be considered result if all items of the given set was matched. This only works if there are no duplicate entries in any of these tables.

Also, instead of the loop use implode.

$string = implode(",", $tagarray);

This way you don't need to bother removing the last comma.