Help - mysql_fetch_array(): 8 is not a valid MySQL result resource

Can anyone help, I have a PHP script that returns a list of products categorys and in doing so it also needs to display the number products within those categories.

The trouble I have is that it displays the first category and products within that category and thats it. The error it comes back with is "mysql_fetch_array(): 8 is not a valid MySQL result resource" however when I remove the following two lines it displays all the categories (but obviously not the number of products within them)

$countResult = mysql_query("SELECT count(*) AS num FROM products WHERE cat_id=$cat_id",$conn);
$num = mysql_result($countResult,0,"num");

The script is:-

function cat_list($conn)
{
global $db_name;
global $db_prod_table;
global $db_cat_table;
$sql = "SELECT * FROM $db_cat_table ORDER BY cat_name";
$result = mysql_query($sql,$conn) or die (mysql_error());

$cat_list ="<table width='800' border='0' cellspacing='2' cellpadding='4'>
<tr>
<td width='50' bgcolor='#000099'><div align='center'><font face='Arial' size='1' color='#FFFFFF'>ID</font></div></td>
<td width='450' bgcolor='#000099'><div align='center'><font face='Arial' size='1' color='#FFFFFF'><b>Category Name</b></font></div></td>
<td width='200' bgcolor='#000099'><div align='center'><font face='Arial' size='1' color='#FFFFFF'><b>Number of products</b></font></div></td>
</td width='50'></td>
</td width='50'></td>
</tr>";

while ($row = mysql_fetch_array($result))
{
extract($row);

// remove the following 2 lines and it displays all the categories
$countResult = mysql_query("SELECT count(*) AS num FROM products WHERE cat_id=$cat_id",$conn);
$num = mysql_result($countResult,0,"num");
$cat_list .=
"<tr>
<td width='50' height='20'><div align='center'><font face='Arial' size='1' color='#FFFFFF'>$cat_id</font></div></td>
<td width='450'><font face='Arial' size='1' color='#FFFFFF'><b>$cat_name</b></font></td>
<td width='200' align='center'><font face='Arial' size='1' color='#FFFFFF'><b>$num</b></font></td>
<td width='50' align='center'>
<b><font face='Arial' size='1' color='#FFFFFF'><a href=confirm_delete.php?cat_id=$cat_id&catProducts=$num>Delete</a></font></b>
</td>
<td width='50' bgcolor='#669900' align='center'>
<b><font face='Arial' size='1' color='#FFFFFF'><a href=modify_cat.php?cat_id=$cat_id>Edit</a></font></b>
</td>
</tr>";
}
$cat_list .="</table>";
return $cat_list;
}

Can anyone help?

I would recommend getting rid of the SQL query within the "while" loop completely.
You can do this by using a query with a "GROUP BY" clause and "COUNT()" function.
It looks like you'll have to use a table join as well.

Below is a breakdown of a query that could work.


MySQL Manual: http://dev.mysql.com/doc/mysql/en/index.html (chapters 10-13!)