I'm trying to fetch the most popular tags from all videos in my database (ignoring blank tags). I also need the 'flv' for each tag. I have this working as I want if each video has one tag:
SELECT tag_1, flv, COUNT(tag_1) AS tagcount
FROM videos
WHERE NOT tag_1=''
GROUP BY tag_1
ORDER BY tagcount DESC LIMIT 0, 10
However in my database, each video is allowed three tags - tag_1, tag_2 and tag_3. Is there a way to get the most popular tags reading from multiple columns?
The record structure is:
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| flv | varchar(150) | YES | | NULL | |
| tag_1 | varchar(75) | YES | | NULL | |
| tag_2 | varchar(75) | YES | | NULL | |
| tag_3 | varchar(75) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
select tag, flv, count(*) as tag_count
from (
select tag_1 as tag, flv from videos
UNION
select tag_2 as tag, flv from videos
UNION
select tag_3 as tag, flv from videos
) AS X
I think that will do it, although there will be double-counting if any record has the same values for two of the tags.
UPDATE: added AS X.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With