Tag system not really working as well as I hoped

[AprilMay]

Hi everyone,

 

So I'm working on a basic tag system, and my tag tables look like this:

 

mysql> show columns from tag_counts;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| tag_id     | int(11) | NO   |     | NULL    |                |
| gallery_id | int(11) | NO   |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+

mysql> show columns from tag_names;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | text    | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

 

When I add a new tag, it gets added to tag_names.  When I tag a gallery, it adds it to tag_counts with the corresponding gallery_id and tag_id. I can guarantee that tag_counts rows are unique, and that a gallery will not be tagged twice with the same tag (ie. gallery 18 will not have two rows in tag_counts both of which correspond to tag_id 11)

 

However, since there is no limit to the number of tags allowed on a gallery or through search, I'll have to build the query through code.

 

If I'm trying to search for a gallery that's been tagged with tag_id 11, 32, and 6, my current SQL statement looks like this:

SELECT gallery_id FROM `tag_counts` where tag_id='11' OR tag_id='32' OR tag_id='6' group by gallery_id HAVING ( COUNT(gallery_id) = 3))

 

My thinking is that using OR will return the galleries where all three ids exist, and since the rows are unique, the galleries with three rows returned are the ones I want.

 

Question: Is there a better way to do this?  This fails if my tag_counts rows aren't unique, and it feels a bit inefficient to find all the galleries that aren't even associated and have it wittled down by count.

 

Thank you in advance for your time and patience!

[AprilMay]

oh boy, when I clicked on the rules, it erased my post.  I had my mySQL version written in my first post! really, i did!

 

my mySQL version is: 5.1.36

 

 

[fenway]

There are many ways to handle this... if it's a limited number, you might just want to CROSS JOIN in multiple times.