hello, there is a tags (varchar) field in the news table (which is sometimes empty), how can you select from the database to select the top 5 most frequently repeated tags from this field? Ie type of the most popular

  • one
    The 'tags' field ... I don’t like the letter 's' at the end, do you have several tags in one field? tags must be in a separate table, one by one in the id-news entry, a tag (or id-tag) - Mike
  • @Mike Or through the cross-table - Anton Shchyrov

1 answer 1

If you have one tag in the tags field, then you need to select records with a non-empty tags field ( WHERE tags IS NOT NULL ), grouped by the tags field ( GROUP BY tags ), count the number of entries in each group ( COUNT(*) ), sort the groups in descending order of quantity ( ORDER BY cnt DESC ) and take the first five groups ( LIMIT 5 )

 SELECT `tags`, COUNT(*) AS `cnt` FROM `news` WHERE `tags` IS NOT NULL GROUP BY `tags` ORDER BY `cnt` DESC LIMIT 5

If it contains several tags, then you need to revise the structure of your database.

  1. Create a separate tags table with the fields id , name
  2. Create a news_tags with the fields new_id , tag_id
  3. Remove from the news table tags field

Then your task will be solved by such a request.

 SELECT t.`name` COUNT(*) AS `cnt` FROM `news_tags` nt LEFT JOIN `tags` t ON (nt.`tag_id` = t`id`) GROUP BY nt.`tag_id`, t`name` ORDER BY `cnt` DESC LIMIT 5

PS If the news tag is still one, it is still better to create a separate tags table, and rename the news.tags field to news.tag_id and make a link to the tags table

  • Complete your answer with an explanation of the decision. - Yuri
  • Thanks, tell me how to make it so that when choosing (in the first example), it doesn’t choose where the field is, but it turns out that emptiness comes first - turik97
  • @ turik97 WHERE tags IS NOT NULL - Anton Shchyrov