开发者

How to realize a navigation through a tag cloud in MySQL

开发者 https://www.devze.com 2023-03-12 17:59 出处:网络
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:

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?

  1. Select image-names from the image table, that have all selected tags in the the tag-table
  2. 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.

0

精彩评论

暂无评论...
验证码 换一张
取 消