Any ideas?

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:

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'";

    }

}

?>

Code (markup):

 

I don't think there is any way to reduce the number of queries that you are doing. You can tune what you have a bit when should be a little better performance-wise.

I would do something like this:

$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);
	
	$query3 = mysql_query("UPDATE multi_db SET forums = '".$count."' WHERE id = '".$array['db']."'");
	
}

PHP:

 

Hi,

Thanks for the help.

That code, it updated the first row in multi_db correctly, setting forums to 3 which is correctly. But set the second row to 1, where it should be around 11.

Thanks!

EDIT//

Hmm, i just tried to see if it was doing the counting right, which it was, echo gives you a 3 and a 8. So its the update?

 

Try this?:

<?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'";

        // My Addition
        mysql_query($query);

    }

}

?>

PHP: