[SOLVED] count items in each category and subcategory

[spidermonster]

ok, ive been working on this like, forever.. actually about a month, to about a month in a half...

anyhow... Ive been to codewalkers, and asked extensively, and they have lost me as a member. Ive been coding for about 4 years, and gone to work for about 1,,, since ive gotten back,, and tried to learn php5,, ive been racking my head against the wall. ok,, what I want, seams extremely simple. I want to list, all my main categories on my page, with counting every item that has been uploaded... a classifieds script. the count on the main page, with the main categories will have the total of all the items in all of its subcategories.

example:

Category1 (4)

Category2 (0)

Category3 (2)

 

Now, I have sube categories for each one, but when I try to add them to the main categories, and not show them, its kinda like it only pics out one subcategory, and ads the items in that one,,, (my theory)

now, im not worried about the subcategories, because I dont need to add them to the main category items.

 

below is the code I have for categories and subcategories to try and get the count of items in the main category and in its subcategories..

right now, for what I have, 1 item in each category and 3 items all together in subcategories... the main category is suppose to show 3 items, and when click that main category it shows a list with one item, and 2 subcategories have an item in both of them...

what am i doing wrong, and is there a way to use less queries for this, I thought of JOIN, but it messes up my count for numrows...

 

 

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 '<font size="2">( '.$n.' ) ';

echo '<font size="2"><b> <a href="http://'.$domain.'/c/';

echo 'results.php?id='.$main_cat_id.'&sub='.$main_cat_sub.'">'.$main_cat_cat.'</a></b><BR>';

 

 

}

--------------------------------------------------------------------------

 

I do not have this code right now in my script, but can give you idea,,, right now, i have nothing in my script, I got mad, and tried starting from scratch...,,, hense,, nothing in my code for categories.

this code only gives me the main categories count of items,, so far.

[btherl]

The first thing i notice is that you are assigning $numofrows_sub_items each time around the while loop, but you only print them after the while loop.  This will result in you only seeing results for the last sub-category returned by your query.

 

It's the inner most while loop I am talking about.  Your code is rather confusing without indentation   Please indent it!

[jitesh]

If you have subcategories upto N Level then use a funda of 'Recursion'.

[Barand]

How are your tables defined?

[spidermonster]

ok, yea, i understand about the indentation, i just copy and past my code alot, and when its done, i clean it up,,, it does help to try to diagnose it...

anyhow... the second while loop was the only way I could think to get the subcategories information for each subcategory. I dont think it pulls all of them though... I believe it only pulls the first one... but anyhow... I dont quite understand what you mean about N Level ,,, and funda of 'Recursion' jitesh.

no offense, old timer with no knowledge on what alot of the terms mean. All I know is it dont work...lol

my tables... ok barand... the wording isnt the same, but the same ordeal for categories table I have:

 

DROP TABLE IF EXISTS `class_categories`;

CREATE TABLE `class_categories` (

  `id` mediumint( NOT NULL auto_increment,

  `sub` mediumint NOT NULL default '0',

  `cat` varchar(255) NOT NULL default '',

    PRIMARY KEY  (`id`)

) TYPE=MyISAM;

 

And my items table is as follows...

 

DROP TABLE IF EXISTS `class_item`;

CREATE TABLE `class_item` (

  `id` mediumint( 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;

 

 

I dont care how the code is done, as long as it works,, ive tried JOIN in the queries,, and other things, but cant go into them all... really, this code is what I first started with,, but, got mad, and started over.. and havent come up with anything else... just,, thought Id start by showing you this code first,,,

all I want to do is get my main categories, not the subcategories to display the main category on the page, with the number of items in that main category, and then add all the items in each of its subcategories.

sounds simple, I thought it was simple,, and swore I had done it before for a search engine script I was working on. I just cant ever seem to keep ahold of my code, like a snippets program... god, that would be nice,,, maybe if I figure this out,, which, I probably wont without help... I'll get a friend of mine to work with Visual Basic and make me a program...lol.. or make it myself... which will take my lifetime...lol

Thanks guys.

[Barand]

try this recursive function

<?php

include 'db2.php';

function cat_item_count($id, $level=0) {
        $sql = "SELECT c.id, c.cat, COUNT(i.cat_id) as num 
                FROM class_categories c 
                    LEFT JOIN class_item i ON c.id = i.cat_id
                WHERE c.sub = '$id' 
                GROUP BY c.id";
        $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
        while (list($id, $cat, $num) = mysql_fetch_row($res)) {
            $indent = str_repeat('--', $level);
            echo "$indent $id $cat ($num items)<br>";
            cat_item_count($id, $level+1);           // call for its subcats
        }
        
}

cat_item_count(0);    call the function for top level categories (parent = 0)
?>

[spidermonster]

Thanks Barand,

I can get this to work, but when I use cat_item_count(0); it gives me all the categories, and subcategories, and their items count. and I like this function because its so small...lol..

but, what Im looking at,, is I cant list all the subcategories on my page because I have so many of them.

 

now,,, we have the sql:

        $sql = "SELECT c.id, c.cat, COUNT(i.cat_id) as num 
                FROM class_categories c 
                    LEFT JOIN class_item i ON c.id = i.cat_id
                WHERE c.sub = '$id' 
                GROUP BY c.id";

 

now, for class_item the only thing i have there for the categories is the cat_id. the sub is in the class_categories. wouldnt that matter...?

Im not used to these joins so, im not sure...

 

my main problem is so far, when I use my script, or yours, I still have the subcategories. I dont want them,,, just the parent categories,,, but I want the total of items for both categories and subcategories...

 

Im trying to play with your code to see if i might be able to get this though,,, but any suggestion since I dont know the JOIN for queries very well... would be great...

 

Thanks again everyone,,, Ive gotten more help here than at codewalkers,, in which im not going back...lol.. even though there are many great people there,, its just small things im tired of...

thanks...

[spidermonster]

PLEASE IGNORE THE ABOVE,,,LOL

 

Ive got how to get just the parent categories, but is there any ideas on getting the total for the categories and subcategories together?

 

what Im wanting is like this,, sorry if anyone misunderstood... my fault..

 

 

 

Parent_cat1 (1)

Sub_cat1    (2)

Sub_cat1    (2)

: Should be :

Parent_cat1 (5)

 

 

 

Parent_cat2 (2)

Sub_cat2    (1)

Sub_cat2    (0)

: Should be :

Parent_cat2 (3)

 

 

 

So, all I would have is the Parent Categories:

 

Parent_cat1 (5)

Parent_cat2 (3)

 

and thanks...

[spidermonster]

no offense for the second post, but another thing I was curious.. Ive changed the code, and on the main page, I get the Parent categories without the subcategories... then when using a link with each of those categories, I go to the results page with the id, sub, and level of the parent category in the link, and get the list of subcategories,,, but unfortunately its not subcategories, its a list of all the categories.. here is what I changed the code to so far...

 

function cat_item_count($id, $level=0) {
        global $domain, $sub;
        $sql = "SELECT c.id, c.sub, c.cat, COUNT(i.cat_id) as num FROM class_categories c LEFT JOIN class_item i ON c.id = i.cat_id WHERE c.sub = '$id' GROUP BY c.id";
        $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
        while (list($id, $sub, $cat, $num) = mysql_fetch_row($res)) {
  		   if($level != 0){
		$indent = str_repeat('    ', $level);
		}

		echo "$indent <a href='http://".$domain."/c/results.php?id=$id&sub=$sub&level=$level'>$cat</a> ($num)<br>";
	   
	   if($level != 0){
	    cat_item_count($id, $level+1);// call for its subcats
		}

        }
        
}

 

It works so far, but again, adding the count of items from the subcategories with the parent category, and on the results page, showing just the subcategory to the parent category with their own count of items without adding the parent categories count of items...

 

hope thats understandable....

mainly right now, all im worried about is the count for the Parent, and the subs... not the results page, because I can use what I have... no biggie,,

 

[Barand]

try

<?php

function cat_item_count($id, $level=0) {
        global $count;
        $sql = "SELECT c.id, c.cat, COUNT(i.cat_id) as num 
                FROM class_categories c 
                    LEFT JOIN class_item i ON c.id = i.cat_id
                WHERE c.sub = '$id' 
                GROUP BY c.id";
        $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
        while (list($id, $cat, $num) = mysql_fetch_row($res)) {
            if ($level==0) $count = 0;
            $count += $num;
            cat_item_count($id, $level+1);           // call for its subcats
            if ($level==0)
                echo "$id $cat ($count items)<br>";
        }
        
}

cat_item_count(0);    //call the function for top level categories (parent = 0)

?>

[spidermonster]

OMG, like, that is so beautiful... after 2 in a half to 3 months it works beautifully.... thanks Barand...

 

I was curious though,,, with the JOIN for the sql...

SELECT c.id, c.cat, COUNT(i.cat_id) as num

                FROM class_categories c

                    LEFT JOIN class_item i ON c.id = i.cat_id

                WHERE c.sub = '$id'

                GROUP BY c.id

 

you have c.id, c.cat, i.cat_id...

is this so that mysql can see that it is two different tables and all? c tells mysql that it goes for one table,, and i for the next table?

 

the rest of it I understand pretty much,, its just been so long since ive worked with php I couldnt remember what worked with what...

[cmgmyr]

yes, that is correct

[Barand]

the "FROM" clause defines the table aliases

 

FROM class_categories c

                    LEFT JOIN class_item i

[spidermonster]

That is so cool... i hate to say it, but im understanding this alot better than I thought I would... I feel like a beginner in what ive been doing for 4 years,,, been out of it for 1 year.... really sucks when so many things have changed, and I didnt learn the main things I needed to begin with,,, I dont see a place to solve this thread, but I think we are done here for at least awhile... now to work on a authentication / membership system... hope to later work on a disntance / miles zipcode system too,,,

so, ill probably be back later... thanks Borand,,, and jitesh, btherl for all your help.. nice to see people out there with what others need...