By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,350 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,350 IT Pros & Developers. It's quick & easy.

Count Number Of Classifieds In Each Category

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. $catquery  = mysql_query("SELECT * FROM categories WHERE subcategory_id = '0' ORDER BY name ASC");
  2.     $catrows = mysql_num_rows($catquery);
  3.  
  4.  
  5.       $query = "SELECT COUNT(title) AS `total` FROM classifieds WHERE classified_id = 'category_id'";
  6. $result = mysql_query($query);
  7.  
  8.  
  9. while($row = mysql_fetch_assoc($result)){
  10. $num_items_in_category = $row['total']; 
  11. }
  12. echo "<><a href='category-".$row['id'].".php' >".$row['name'].$num_items_in_category."</a></li>";
  13.  
Thanks guys
May 10 '12 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,369
I can't tell if it's right or wrong without any sample data.
May 10 '12 #2

P: 5
What data do you want me to provide?
May 10 '12 #3

Rabbit
Expert Mod 10K+
P: 12,369
I need to see a sampling of data in the tables.
May 10 '12 #4

P: 5
Ok, here is database structure and its data.
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE IF NOT EXISTS `categories` (
  2.   `id` int(255) NOT NULL AUTO_INCREMENT,
  3.   `name` text COLLATE utf8_unicode_ci NOT NULL,
  4.   `subcategory_id` int(2) NOT NULL DEFAULT '0',
  5.   `parent_id` int(255) NOT NULL,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27 ;
  8.  
  9. --
  10. -- Dumping data for table `categories`
  11. --
  12.  
  13. INSERT INTO `categories` (`id`, `name`, `subcategory_id`, `parent_id`) VALUES
  14. (1, 'Announcements', 0, 0),
  15. (2, 'Employment', 0, 0),
  16. (3, 'Items For Sale', 0, 0),
  17. (4, 'Services', 0, 0),
  18. (5, 'Garage Sales', 0, 0),
  19. (6, 'Automobiles', 0, 0),
  20. (7, 'Announcement1', 1, 1),
  21. (8, 'Announcement2', 1, 1),
  22.  
  23. --
  24. -- Table structure for table `classifieds`
  25. --
  26.  
  27. CREATE TABLE IF NOT EXISTS `classifieds` (
  28.   `classified_id` int(255) NOT NULL AUTO_INCREMENT,
  29.   `title` text COLLATE utf8_unicode_ci NOT NULL,
  30.   `description` text COLLATE utf8_unicode_ci NOT NULL,
  31.   `category_id` int(10) NOT NULL,
  32.   `name` text COLLATE utf8_unicode_ci NOT NULL,
  33.   `authorized` int(10) NOT NULL DEFAULT '0',
  34.   PRIMARY KEY (`adid`)
  35. ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=177 ;
  36.  
  37. --
  38. -- Dumping data for table `classifieds`
  39. --
  40.  
  41. INSERT INTO `classifieds` (`classified_id`, `title`, `description`, `category_id`, `name`, `authorized`) VALUES
  42. (1, 'Test Classified', 'Here is the First Test classified listing.', 1, 1);
  43.  
  44. INSERT INTO `classifieds` (`classified_id`, `title`, `description`, `category_id`, `name`, `authorized`) VALUES
  45. (2, 'GMC For Sell', 'Looks like new 1979 GMC.', 6, 1);
Thanks again
May 10 '12 #5

Rabbit
Expert Mod 10K+
P: 12,369
You should use a query that returns an aggregate query instead. Something like
Expand|Select|Wrap|Line Numbers
  1. SELECT groupingField, COUNT(*) AS countOfGroup
  2. FROM someTable
  3. GROUP BY groupingField
May 11 '12 #6

P: 5
Thanks, I tried it this way:
Expand|Select|Wrap|Line Numbers
  1.  $query = "SELECT category_id, COUNT(title) FROM classifieds GROUP BY category_id"; 
  2. $num_items_in_category = $row['COUNT(title)'];
  3. echo "<><a href='category-".$row['id'].".php' >".$row['name'].$num_items_in_category."</a></li>";
  4.  
According to data above, I'm getting (2) in each category.
So far I am getting there.
May 11 '12 #7

Rabbit
Expert Mod 10K+
P: 12,369
You don't seem to be looping your recordset at all so I'm not surprised you get the same number every time.
May 11 '12 #8

P: 5
Oh sorry I just didn't put the whole code:
Expand|Select|Wrap|Line Numbers
  1.  $query = "SELECT category_id, COUNT(title) FROM classifieds GROUP BY category_id"; 
  2. $result = mysql_query($query) or die(mysql_error());
  3. while($row = mysql_fetch_array($result)){
  4. $num_items_in_category = $row['COUNT(title)'];
  5. }
  6. echo "<><a href='category-".$row['id'].".php' >".$row['name'].$num_items_in_category."</a></li>";
May 11 '12 #9

Rabbit
Expert Mod 10K+
P: 12,369
You never print the results within the loop so you're only going to get the last value.
May 11 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.