[RESOLVED] How to display how many items are in a category?

Scooby_Doo

I want to display how many items are in a category like this site here.

http://www.whateverlife.com/myspacelayouts.php

Also how can I make it where each page maxes out at 50 like hers.

Thanks in advance

bpat1434

Simple. Just query for the count of each item....

SELECT COUNT(*) AS `total`
FROM `table`
GROUP BY `categoryid`

Then just loop through the results. Should give you each category, and the number of rows for each 😉

Scooby_Doo

How would I put that in this coding.

$result = mysql_query("
SELECT DISTINCT
l.categoryid , lc.name, lc.id
FROM 
`layouts` AS l
LEFT JOIN 
`lcategories` AS lc
 ON l.`categoryid` = lc.`id`
ORDER BY lc.`name`
");
while($row = mysql_fetch_assoc($result))
{
    echo "<a href='/category.php?categoryid={$row['categoryid']}'>";
	echo "<font size=\"36\">";
	echo "Category: ";
	echo $row['name'];
// This is where I want it to display the number (example: (26) )
	echo "</font>";
	echo "</a>";
	echo "<br>";
}

The reason I don't know is because I'm doing a join. Thanks for the response

bpat1434

I don't believe you can without using a subquery. The DISTINCT keyword is what's going to get you.

So you can either run the query on its own (it's a fast query), or use a subquery and include it's output in the response:

$result = mysql_query("
SELECT DISTINCT
l.categoryid , lc.name, lc.id, cnt.`total`
FROM
`layouts` AS l
LEFT JOIN
`lcategories` AS lc
ON l.`categoryid` = lc.`id`
LEFT JOIN (
   SELECT COUNT(*) AS `total`, `categoryid`
   FROM `layouts`
   GROUP BY `categoryid`
) AS cnt
    ON lc.`id` = cnt.`categoryid`
ORDER BY lc.`name`
");

Scooby_Doo

Thank You so much. It works.