phpmysqltagging
Get most popular tags from mysql normalized tables
I'm building my own tag system for a forum I'm making. Everything is working perfectly, but I am trying to echo a list of the most popular tags but I can't find which query to use..
My tables look like this:
I need a list of the 20 most popular tags, so the tag_names of which the tag_id appear the most in the article_tag_xref table. Anyone who has an idea what the query should look like? Thanks!
Solution
You can use the following query:
SELECT t.tag_id, t.tag_name, COUNT(article_id) AS cnt
FROM Article_Tag_Xref AS a
INNER JOIN Tag AS t ON a.tag_id = t.tag_id
GROUP BY t.tag_id, t.tag_name
ORDER BY COUNT(article_id) DESC LIMIT 20
COUNT(article_id) returns the number of appearances of each tag_ig in the Article_Tag_Xref table. Thus, order by this count in descending order and applying a LIMIT 20 returns the 20 most popular tag_ig values.
- Closures in PHP... what, precisely, are they and when would you need to use them?
- Overriding the "email" attribute on Notifiable Trait Laravel
- OCI8 working with the command line but not on apache
- How to call custom helper in laravel 5?
- How to read specific lines from a text file in PHP
- Does PHP have an equivalent to Python's all()
- After filtering, check if array is not empty after filtering or has all original values; like Javascript's some() and every()
- Why variable can not start with a Number?
- How to clone object to child class in php
- Make adding custom instrumentation in Sentry less verbose
- Multidimensional array sorting by value in php
- Sort an array of single-element arrays by value descending and preserve keys
- Are there any ways to press the Enter keyboard key?
- Sort each subarray of a 2d array by delimited string values
- How to get Route name in Laravel 8
- Installing oci8 on Centos 8 running Apache and php-fpm
- Symfony UX chart does not show chart in easyadmin
- Using additional data in php exceptions
- I'm trying to format a response from an api but i keep getting Warning: Undefined array key "timestamp"
- Distribute child elements from a multidimensional array as rows including parent values in a new 2d array
- How to create custom helper functions in Laravel
- How do i sort an array of array in PHP?
- Process every two elements in a flat array
- Cast specific array values to array type
- How to Migrate and seed before the full test suite in Laravel with in memory database?
- PHP openssl_pkcs12_read "error:0308010C:digital envelope routines::unsupported"
- WooCommerce API giving JSON Syntax Error on every request
- ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
- Remove prefix from keys of an associative array and use the prefix as the first level key of a grouped 2d array
- Convert numeric words embedded in strings into their integer equivalent, then validate the mutated strings