I want to write a web application to order my photos. Instead of putting them into categories, I want to add tags to certain pictures. I thought of having 2 tables, one for the images:
uniqueID imagename
1 photo1.jpg
2 photo2.jpg
3 photo3.jpeg
4 pic4.jpeg
and one table for the tags:
ID tag
1 开发者_Python百科 California
1 Peter
1 beach
2 California
2 L.A.
2 Peter
3 California
3 Susan
4 Kansas
4 Claudia
After starting the application the tags are shown in a size accoring to the number of occurences in the database. When I click on a tag (e.g. "California") all the remaining tags of images, that also have the selected tag should be shown on the next page (here: "Peter", "beach", "L.A.", "Susan" but NOT "Kansas" and "Claudia"). When I afterwards click for example on the tag "peter" all the remaining tags of photo 1 and 2 should be shown ("beach", "L.A.").
Is it possible to realize those two things with a single query?
- Select image-names from the image table, that have all selected tags in the the tag-table
- Select all remaining tags and their COUNT() for those images (IDs) that also have the selected tags in the tag-table.
Thanks for your help
Sven
For 1) You must use a SELECT query with JOINS. For example:
SELECT *
FROM images i
INNER JOIN categories c
ON i.id = c.id
For 2) I'm not quite sure I understand what you mean by remaining tags.
精彩评论