Need help with MySQL query and PHP

Question

I have the following 3 tables:

(PK = primary key, FK = foreign key)

File table

File ID (PK)    File Name    ...
------------    ---------
     1            a.jpg      ...
     2            b.png      ...
     3            c.jpg      ...
     .              .
     .              .
     .              .

Tag table

Tag ID (PK)   Tag Name       ...
-----------   ----------  
   1          Melbourne      ...
   2          April          ...
   3          2010           ...
   .           .
   .           .
   .           .

Files_Tags table

File ID (FK)    Tag ID (FK)
------------    -----------
      1              1
      1              5
      1              7 
      2              2 
      2              4
      3              3
      .              .
      .              .
      .              .

In PHP, I want to get a list of all tags along with the number of times a tag appears (i.e. the number of files that have this tag).

Is it possible to do this with a single MySQL query?

Answer 1

Try GROUP BY on your tag id. Use LEFT JOIN to include tags that exist in the tag table but are never used.

SELECT
    Tag_Name, 
    COUNT(Files_Tags.Tag_ID) AS cnt
FROM Tags
LEFT JOIN Files_Tags
ON Tags.Tag_ID = Files_Tags.Tag_ID
GROUP BY Tags.Tag_ID

Result:

Melbourne 1
April 1
2010 1
... ...

ORDER BY Tag_Name ORDER BY COUNT(*), , .

---

, . . , LEFT JOIN INNER JOIN:

SELECT t.tag_id,
       t.tag_name,
       IFNULL(d.tag_count, 0) AS tag_count
FROM tags t
LEFT JOIN
(
    SELECT tag_id, COUNT(*) tag_count
    FROM files_tags
    GROUP BY tag_id
) d ON d.tag_id = t.tag_id;

Answer 2

GROUP BY, ORDER BY * JOIN, , , .

, SELECT PHP-. , .

, 1 .

SELECT * FROM "tags_table".

php foreach, "files_tags":

SELECT FILE_ID COUNT(*) FROM TAGS_TABLE WHERE TAG_ID = 'tag_uid'

, , , .