update problem

[slimjim]

Having a problem updating.
First i do a count of the categoryg and group them.
I want to place the count of each group in the colum count in table category, and if there were no categorys in subcat categoryg that match category table then make those a zero otherwise update the count in the other categorys.

Can someone help me ?

$query3 = "SELECT categoryg, count(*) as `count` FROM subcat GROUP BY categoryg";
$result3 = mysql_query($query3) or die( "ERROR: " . mysql_error() . "\n");
while($row = mysql_fetch_array($result3)) {
$catcount = $row['count'];
print($catcount);
}

$query4 = "UPDATE category SET count='$catcount' WHERE category='$categoryg'";
$result4 = mysql_query($query4) or die( "ERROR: " . mysql_error() . "\n");

//This is my problem. query4 is only updating the categorys it has counted and not putting others at zero. I am trying to make it so that i can have a good count of links in each category
}

[dklynn]

slimjim,

I got lost in your description but I believe I know what you're asking for: WHY no updates (to zero) for the categoryg that is not found.

The reason it's not updated is that it's not found.

Three solutions:

1. Enter 0 into the count table for all categorygs THEN perform your count (updating all those categorygs found) OR

2. Use the count table to select the categoryg to count then update to the count value for that category OR

3. Save your count values to an array (initialized with zeros) and then update your count table with that array).

Option 1 would be the simplest, Option 2 requires many passes through the db which Option 3 would avoid.

Regards,