Cateories with number of rows

[ando321]

Im trying to while loop my categories with another table called product so multiple table.

 

I wanted to display my categories like the following:

 

cat1 (658)

cat2 (556)

etc

 

Here is the select clause im using, but it only seems to display the category name???

 

$numprodscat ="SELECT pcatid,pcatname,

  (SELECT COUNT(*) FROM product WHERE parents_id = c.pcatid) as 'count'

FROM parent_cats AS c";

 

thanks

[ando321]

Ok i have this:

 

$numprodscat="select cats.pcatid,cats.pcatname,

  (select count(*) from product as nums where nums.parents_id = cats.pcatid)

FROM parent_cats as cats";

 

How do i display the count of the product of that category in my php script, ive tryed  mysql_num_row() but that just displays the number of parent categories i have....

 

thanks

[phparray]

I strongly believe what you are trying to do can not be done with a subquery because count(*) only returns one row and you need more than one row of data.  This would be far better as separate queries.

 

#not tested

$results = mysql_query('select pcatid,pcatname from parent_cats');
while($row = mysql_fetch_row($results))
{
  $r2 = mysql_query('select count(*) from products where parents_id = "$row[0]";');
  $num = mysql_result($r2,0,'count(*)');
    echo $row[1] . '('. $num .') <br />';
}

mysql_free_result($results);
mysql_free_result($r2);

 

[fenway]

I strongly believe what you are trying to do can not be done with a subquery because count(*) only returns one row and you need more than one row of data.  This would be far better as separate queries.

COUNT() return a single value, not a single row -- try this:

 

SELECT c.pcatid,c.pcatname, p.cnt
FROM parent_cats AS c
INNER JOIN 
( SELECT parents_id, COUNT(*) AS cnt FROM product GROUP BY parents_id ) AS p ON ( p.parents_id = c.pcatid )