Update multiple/ mysql headache.

[a1amattyj]

Hello,

 

Right, ive been trying to create a query but ive tried soo many and got so confused so wondering if anyone can help me here!

 

 

What i want to do:

 

Table : multi_forums

Multiple Rows, Primary Key = ID (AUTO INC)

Each row has a value of 'db' which is the Primary ID of another table.

 

Table : multi_db

Multiple Rows, Primary Key = ID (AUTO INC),

Which each row in Multi_forums has set. (See above).

Value named 'forums'.

 

Basically, count how many rows from 'multi_forums' use each 'multi_db' ID. Then update the value of 'forums' in 'multi_db' with the result.

 

 

Thanks Again!

 

What i have tired:

 

<?php

require ('../multi_operations/config_inc.php');

$result = mysql_query("SELECT * FROM multi_forums");

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

    $dbidd = $row['db'];

    $result = mysql_query("SELECT * FROM multi_forums WHERE db = '{$dbidd}'");

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

        $num_rows = mysql_num_rows($result);

        $query = "UPDATE multi_db SET forums = '{$num_rows}' WHERE id = '$dbidd'";

    }

}

?>

[a1amattyj]

Using:

 

$query = mysql_query("SELECT DISTINCT db FROM multi_forums");

while($array = mysql_fetch_array($query))
{
   
    $query2 = mysql_query("SELECT COUNT(id) FROM multi_forums WHERE db = '".$array['db']."'");
    $count = mysql_result($query2,0);
   
echo $count;
    $query3 = mysql_query("UPDATE multi_db SET forums = '".$count."' WHERE id = '".$array['db']."'");
   
}

 

Where i echo the count, it echos the correct values.

 

The update doesn't update properly. It updates the first row in multi_db correct, but leaves the second one with a '1'.

 

Thanks.

[a1amattyj]

*bump*