Count items from subcategories

[sharke]

Hi guys, i need help for count total items from subcategory.

 

table: items

items_cat_id

 

table: category

id | catname | parent

1    Test          0

2    PHP            1

3    Mysql          1

4    Test2          0

5    Javascrip    4

6    Ajax            4

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

explame, how can count Javascript and Ajax from Test2 ?

[megaresp]

I think you want a query that counts all the children of a specific category. If so, your query is...

 

select count(*) as tot_cats from category where parent='category.id';

 

In the above query, replace category.id with the actual id. For example, to count the number of sub-categories (children) of Test2, your query is...

 

select count(*) as tot_cats from category where parent='4';

 

If you want a count for every category with children, your query is...

 

select catname, count(*) as tot_cats from category where parent > '0' group by catname;

[sharke]

this count only children, but i need count total items from Javascript and Ajax

 

total items:

Javascript = 8

Ajax = 3

[JustLikeIcarus]

Try this

 

select category.name, count(*)

from items, category

where category.id = items.item_cat_id

and category.name IN ('Javascript', 'Ajax')

group by category.name

[sharke]

result: Resource id #16

[JustLikeIcarus]

Woops. try this

 

select category.name, count(items.*)

from items, category

where category.id = items.item_cat_id

and category.name IN ('Javascript', 'Ajax')

group by category.name

[JAY6390]

Could you paste the actual table structures?

Are you trying to find the total number of items within the categories of Test 2 by only supplying test2's id?

[sharke]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from items, category WHERE category.id = items.cat_id and category.' at line 1

 

Woops. try this

 

select category.name, count(items.*)

from items, category

where category.id = items.item_cat_id

and category.name IN ('Javascript', 'Ajax')

group by category.name

 

JAY6390 - i need this

total items:

Javascript = 8

Ajax = 3

 

result of total items from subcategories (Javascript and Ajax)

[JustLikeIcarus]

Ok it must not like count(items.*) you can change the * to a specific col in the items table like count(items.id) if you have a column named id.

[JAY6390]

Try this

SELECT *, (SELECT COUNT(items.item_cat_id) as `cnt` FROM items WHERE item_cat_id = categories.id) FROM categories WHERE parent = 4

[sharke]

JAY6390, JustLikeIcarus - i get this message -> Resource id #16

 

[JAY6390]

ok well thats jus the resource handle for the results. You now need to extract the data using mysql_fetch_assoc. I'm guessing you haven't worked with php & mysql much/at all

[JustLikeIcarus]

While your testing queries you may find it easier to use phpmyadmins SQL form or a local mysql GUI app that will show you results

[sharke]

Soryy i'm noob

i extract the data, but still get error: Notice: Undefined index: cnt in

 

<?php
$result = mysql_query("SELECT *, (SELECT COUNT(items.item_cat_id) as cnt FROM items WHERE item_cat_id = categories.id) FROM categories WHERE parent = 4");
$data = mysql_fetch_assoc($result);
echo $data['cnt'];

ok well thats jus the resource handle for the results. You now need to extract the data using mysql_fetch_assoc. I'm guessing you haven't worked with php & mysql much/at all

[JustLikeIcarus]

If your going to do it that was the "as cnt" needs to come after the query in parenthesis like

SELECT *, (SELECT COUNT(items.item_cat_id)  FROM items WHERE item_cat_id = categories.id) as cnt FROM categories WHERE parent = 4"

 

Im not sure if doing it that way will bring back the result you want. it may.

[JAY6390]

it should do yea

[sharke]

strange, no errors anymore but in table items i have 1 query (items_cat_id (Javascript)), i get result = 0

[sharke]

<?php
$result = ("SELECT *, (SELECT COUNT(items.item_cat_id)  FROM items WHERE item_cat_id = categories.id) as cnt FROM categories WHERE parent = 1");
while ($data = mysql_fetch_assoc($result)){
echo $data['cnt'];
}

now i get this result -> 000000100

 

In this category i have 9 sub-categories, i check items table from datebase how many query i have from those sub-categoris and i have 1 query from sub-category (7).

[fenway]

What's wrong with a standard LEFT JOIN?

[sharke]

error ->Unknown column 'categories.id' in 'where clause'

 

can you example with code ?

[sharke]

your code working perfectly, it is was my fault

Thank you very much!

 

If your going to do it that was the "as cnt" needs to come after the query in parenthesis like

SELECT *, (SELECT COUNT(items.item_cat_id)  FROM items WHERE item_cat_id = categories.id) as cnt FROM categories WHERE parent = 4"

 

Im not sure if doing it that way will bring back the result you want. it may.