MySQL count from intermediary table

Question

I'm trying to find the most used categories in an intermediary table which contains two ids, I'm running this to find all the counts of posts against categories.

$data = $conn->query('SELECT * FROM category WHERE category_name ORDER BY category_name');

foreach($data as $row) {

$sql = "SELECT COUNT(p_id) FROM p_categories c,category ca WHERE c.category_id = ca.category_id AND category_name = :category_name";
$q = $conn->prepare($sql);
$q->execute(array(':category_name' => $row['category_name']));
$catco = $q->fetch();

echo '<p>'.$row['category_name'].' ('.$catco[0].')</p>';
}

This returns all the categories, but I want to limit it to only the top four categories having most p_id. I tried limiting the count query to 4 but that didn't work.

Here are the tables:

posts: p_id, p_name

p_categories: p_id, category_id

category: category_id, category_name

Steve Steve · Accepted Answer · 2015-03-25T22:34:41

You can do this in a single query

$sql = "SELECT p_categories.category_id, COUNT(*) AS cnt, category.category_name
        FROM p_categories 
        LEFT JOIN category
        ON p_categories.category_id = category.id 
        GROUP BY `category_id` 
        ORDER BY cnt 
        DESC LIMIT 4"

$data = $conn->query($sql); 
foreach($data as $row) {
    echo '<p>'.$row['category_name'].' ('.$row['cnt'] .')</p>';
}

MySQL count from intermediary table

1 Answers