Count Number Of Classifieds In Each Category

[Rocks]

Hi there, I have a table called categories that contains: id, name, subcategory_id, parent_id.
And another table classifieds that contains: classified_id, title, category_id.
I am trying to pull out number of item in each category. For example:
Accessories(15)
Auto(11)
Tutorials(25)
I gave it a try as follow, but all I got was zeros(0) even thought I added some items in categories:

$catquery  = mysql_query("SELECT * FROM categories WHERE subcategory_id = '0' ORDER BY name ASC");
    $catrows = mysql_num_rows($catquery);
 
 
      $query = "SELECT COUNT(title) AS `total` FROM classifieds WHERE classified_id = 'category_id'";
$result = mysql_query($query);
 
 
while($row = mysql_fetch_assoc($result)){
$num_items_in_category = $row['total']; 
}
echo "<><a href='category-".$row['id'].".php' >".$row['name'].$num_items_in_category."</a></li>";
 

Thanks guys

[Rabbit]

I can't tell if it's right or wrong without any sample data.

[Rocks]

What data do you want me to provide?

[Rabbit]

I need to see a sampling of data in the tables.

[Rocks]

Ok, here is database structure and its data.

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `subcategory_id` int(2) NOT NULL DEFAULT '0',
  `parent_id` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27 ;
 
--
-- Dumping data for table `categories`
--
 
INSERT INTO `categories` (`id`, `name`, `subcategory_id`, `parent_id`) VALUES
(1, 'Announcements', 0, 0),
(2, 'Employment', 0, 0),
(3, 'Items For Sale', 0, 0),
(4, 'Services', 0, 0),
(5, 'Garage Sales', 0, 0),
(6, 'Automobiles', 0, 0),
(7, 'Announcement1', 1, 1),
(8, 'Announcement2', 1, 1),
 
--
-- Table structure for table `classifieds`
--
 
CREATE TABLE IF NOT EXISTS `classifieds` (
  `classified_id` int(255) NOT NULL AUTO_INCREMENT,
  `title` text COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(10) NOT NULL,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `authorized` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`adid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=177 ;
 
--
-- Dumping data for table `classifieds`
--
 
INSERT INTO `classifieds` (`classified_id`, `title`, `description`, `category_id`, `name`, `authorized`) VALUES
(1, 'Test Classified', 'Here is the First Test classified listing.', 1, 1);
 
INSERT INTO `classifieds` (`classified_id`, `title`, `description`, `category_id`, `name`, `authorized`) VALUES
(2, 'GMC For Sell', 'Looks like new 1979 GMC.', 6, 1);

Thanks again

[Rabbit]

You should use a query that returns an aggregate query instead. Something like

SELECT groupingField, COUNT(*) AS countOfGroup
FROM someTable
GROUP BY groupingField

[Rocks]

Thanks, I tried it this way:

 $query = "SELECT category_id, COUNT(title) FROM classifieds GROUP BY category_id"; 
$num_items_in_category = $row['COUNT(title)'];
echo "<><a href='category-".$row['id'].".php' >".$row['name'].$num_items_in_category."</a></li>";
 

According to data above, I'm getting (2) in each category.
So far I am getting there.

[Rabbit]

You don't seem to be looping your recordset at all so I'm not surprised you get the same number every time.

[Rocks]

Oh sorry I just didn't put the whole code:

 $query = "SELECT category_id, COUNT(title) FROM classifieds GROUP BY category_id"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$num_items_in_category = $row['COUNT(title)'];
}
echo "<><a href='category-".$row['id'].".php' >".$row['name'].$num_items_in_category."</a></li>";

[Rabbit]

You never print the results within the loop so you're only going to get the last value.