help with count

slimjim

OK I have this code below that gets my categorys. Only one problem though. I also need to get the count from another table for each category.
Where it says $count on the below code, i need it to show the count from a table called subcat and field called categoryg.
category from table category and categoryg from subcat table are same.
So i need the count from subcat where categoryg == category

Table category
id | category
Table subcat
subcatid | categoryg | link

But have no clue on how to edit this so it works.

/* Select all categories from category table */
  $query = "SELECT * FROM category ORDER BY catid ASC";
  $result = mysql_query($query)
       or die ("Couldn't execute query.");
$i = 0;
while ($row = mysql_fetch_array($result))
  { 
     $i++;
     if($i == 1)
     {
        echo "<tr>";
     }
     extract($row);
     echo "<td bgcolor=\"#f2f2f2\" valign='top' width='22%' class='product'>\n";
     echo "<img src=\"$SITE_URL/images/folder.gif\" align=\"Middle\" width=\"31\" height=\"30\">&nbsp;<a href=\"$SITE_URL/Dir/index.php?cate=$category\">$category</a> ($count)</font>
          </td>
          ";
     if($i == 3)
     {
       echo "</tr>";
       $i=0;
      }
  }

PallaviDalvi

not very sure(never am when databases are in question)...but i think u shud be having another query in the while loop....
something like
select count(*) from subcat where categoryg = category
so for each category read from the outer query(i assume the categories returned to be unique) the counts are picked up from the subcat table. i hope i understood the prob correctly and was of some use.

drawmack

<?php
  $query  = "SELECT a.id,a.category,count(b.categoryg) ";
   $query .= "FROM category as a,subcat as b ";
   $query .= "WHERE a.id = b.categoryg ";
   $query .= "GROUP BY b.categoryg ";
   $query .= "ORDER BY catid ASC";
?>

slimjim

Not sure where in the code i would put that query or how to show results. Can you edit my code please if you dont mind doing it for me. I been killing myself over this LOL

Thanks