count each category

fibonacci

heyya..

i need help..
I wanted to display the categories of classified with the total of data in each category. But i don't get the idea how to count the total of data in each category for example;

rent/sell [5]
job vacancy [7]
car[4]

here is my code:


$stmt = "SELECT categoryid, name FROM classified_category ";
$query = mysql_query($stmt)or die(mysql_error());


while($rs=mysql_fetch_array($query)){

    $categoryid = $rs['categoryid'];
    $name = $rs['name'];

    echo "<ahref=classified.view-all.php?categoryid=".$categoryid."><b>".$name."</b></a>";
    echo "<br />";

    }

?>

thanks in advance!

Flyier

http://dev.mysql.com/doc/refman/5.0/en/counting-rows.html

alimadzi

You probably also want to change 'ahref' to 'a href' and put quotes around the URL. Isn't 'ahref' a form of sneezing? Ah-ah-ah... AHREF!!! Excuse me... 😉

fibonacci

hi, thanks flyier for the link..
i have tried it using the query below.

SELECT cc.categoryid, cc.name, COUNT( * )
FROM classified_category cc, classified c
WHERE cc.categoryid = c.categoryid
GROUP BY name, categoryid

using phpmyadmin, i query this and get the result as below,

categoryid name count(*)
1 vacancy 10
2 sell 15

how i can display the 'count' values using php?

bpat1434

$query = "SELECT cc.categoryid, cc.name, COUNT( * ) AS `count`
FROM classified_category cc, classified c
WHERE cc.categoryid = c.categoryid
GROUP BY name, categoryid ";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))
{
  echo $row['name'] . ' ' . $row['count'] . '<br />';
}