categories, and subcategories count

spidermonster · March 10, 2007, 4:59pm

Ok, I have changed my code recently and started over,
It may not be the prettiest code, but its functional…
I have been working on this code for over a month, and cant come up with anything… Ill rewrite the code later to make it better, nicer looking and hopefully faster,
anyhow… here is my code…

[php]
echo “

”; <?php $query = "SELECT * FROM class_categories WHERE sub='0' ORDER BY cat"; $result = mysql_query($query); $num_rows = mysql_num_rows($result); echo ''; for($i = 0; $i < $num_rows; $i++) { $row = mysql_fetch_array($result); $query1 = "SELECT * FROM class_item WHERE cat_id=".$row['id'].""; $result1 = mysql_query($query1); $num_rows1 = mysql_num_rows($result1); if($i % $columns == 0) { echo "n"; } echo ' '; if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) { echo "n"; } } echo '

('.$num_rows1.') '.$row['cat'].'

'; echo '

'; ?>

[/php]

now, of course Im working with categories and subcategories, and here is mysql schema…

DROP TABLE IF EXISTS `class_categories`;
CREATE TABLE `class_categories` (
  `id` mediumint(8) NOT NULL auto_increment,
  `sub` mediumint NOT NULL default '0',
  `cat` varchar(255) NOT NULL default '',
    PRIMARY KEY  (`id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `class_item`;
CREATE TABLE `class_item` (
  `id` mediumint(8) NOT NULL auto_increment,
  `cat_id` mediumint NOT NULL default '0',
  `email` varchar(255) default NULL,
  `title` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `price` varchar(255) NOT NULL default '',
  `uid` varchar(255) NOT NULL default '',
  `d_start` varchar(255) NOT NULL default '',
  `d_stop` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `zip` mediumint(5) NOT NULL,
  `image` VARCHAR(30) NOT NULL,
   PRIMARY KEY  (`id`)
) TYPE=MyISAM;

Ok, with all this, what Im doing, is when a users comes to my site, and they see the main categories, with the main categories, I wanted to put how many items with each main category, plus each subcategory,
the code above doesnt have the subcategory links,

I have tried different code before, without the table column seperation… but thought if I had the subcategories listed with the main categories, they wouldnt have to see all of the items added together in just the main category, but I have so many subcategories with the main category, it fills up the entire page with just categories, so, just went back to having the main categories on the page, which is the code above, the table can actually be taken out, because, I can just make them a list on the main page…

any help with this would be greatley appreciated…

Zyppora · March 10, 2007, 7:52pm

Can try something with the aggregate function COUNT or SUM? That’s really the only thing I can think about. These can be used in the SELECT part of the query too btw, and don’t require GROUP BY.

spidermonster · March 11, 2007, 12:03am

This is the code I first started with. But for some reason when I use count(*) It does the same as numrows technicly, but for some reason it just doesnt give me the exact number of subcategories items… I can either get 1, or the amount of categories themselves… Sum ive never used before… And it seams so simple that its rediculous… I mean, all I want, is to take one of the main categories, count its total items, then take the subcategories underneath that same category, and count their items, then add them all together… so, if I have one item under the main category, and I have 3 under its subcategories, I should have a total of four… I can get the one from the main categories, but it seams like I have to echo the subcategories somehow to get the total in each of them to count them, but I have so many subcategories that I cant show them on the page with echo… hold on, let me try to reuse this code here, and just start with counting the main categories again, this way I kinda start with scratch, then Ill try to add the subcategories, and list the code…

[php]

<? $query_main_cat = "SELECT * FROM class_categories WHERE sub='0' ORDER BY cat"; $result_main_cat = mysql_query($query_main_cat); $numrows_main_cat = mysql_num_rows($result_main_cat); while($main_cat = mysql_fetch_array($result_main_cat)){ $main_cat_id = $main_cat['id']; $main_cat_sub = $main_cat['sub']; $main_cat_cat = $main_cat['cat']; $query_sub_cat = "SELECT * FROM class_categories WHERE sub='$main_cat_id' ORDER BY cat"; $result_sub_cat = mysql_query($query_sub_cat); $numrows_sub_cat = mysql_num_rows($result_sub_cat); $query_main_items = "SELECT * FROM class_item WHERE cat_id='$main_cat_id'"; $result_main_items = mysql_query($query_main_items) or die ("Query_Items failed"); $numofrows_main_items = mysql_num_rows($result_main_items); while($sub_cat = mysql_fetch_array($result_sub_cat)) { $sub_cat_id = $sub_cat['id']; $sub_cat_sub = $sub_cat['sub']; $sub_cat_cat = $sub_cat['cat']; $query_sub_items = "SELECT * FROM class_item WHERE cat_id='$sub_cat_id'"; $result_sub_items = mysql_query($query_sub_items) or die ("Query_Items failed"); $numofrows_sub_items = mysql_num_rows($result_sub_items); } $n = $numofrows_sub_items+$numofrows_main_items; echo '( '.$n.' ) '; echo ' '.$main_cat_cat.'
'; } ?>

[/php]