Need help with MySQL query & PHP

Question

I have the following 3 tables:

(PK = Primary Key, FK = Foreign Key)

Files Table

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

Tags 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 the tag appears (i.e. the number of files that have this tag).

Is that possible to do with one MySQL query ?

Answer 1

Try GROUP BY on your tag id. Use a LEFT JOIN to include tags that exist in the tags table but aren't ever 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
...        ...

You may also want to add an ORDER BY Tag_Name or an ORDER BY COUNT(*) if you want the rows returned in sorted order.

---

Daniel Vassello also submitted an answer but deleted it. However his answer is quite easy to adapt to meet your new requirements. Here is his solution, modified to use a LEFT JOIN instead of an 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

You shouldn't use too much of GROUP BY, ORDER BY and * JOIN as those query are very heavy and it's not something you should base your code on.

If I was you, I would do multiple simple SELECT query and group the stuff together using PHP algorithms. This way, you're DB won't be hit by very slow query.

So basically, in your specific question I would have more than 1 query.

I would start by doing a

SELECT * FROM "tags_table".

In php, I would created a foreach loop that would count appearance of every tag in your "files_tags" table:

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

It's mostly pseudo-code so I wouldn't expect those query to work but you get the general idea.