Recursive sum in request
I have a table with a stored data hierarchy using an adjacency list like in the following example
id account parent 1 root null 2 a1 1 3 b1 1 4 a2 2 5 a3 4 6 a4 2 7 b2 3
And the table where I store the values for these accounts
id_account value 2 10 2 10 4 20 5 30 6 10
I made a function that returns the entire child account given the parent account:
function getChildrenAccount($parent_id)
{
$query = "SELECT id, account FROM accounts WHERE parent='{$parent_id}' ORDER BY account";
$result = mysql_query($query) or die(mysql_error());
while($r[]=mysql_fetch_assoc($result));
return $r;
}
What I am trying to do is a function that returns not only the child accounts, but the sum of all values, including the children of each result. for example
getChildrenAccount(4)
Will return an array with the following syntax
array (1) { [0] => array (3) { ["id"] => 5 ["account"] => "a3" ["sum"] => 50 // a2 + a3 }
AND
getChildrenAccount(2)
array (2) { [0] => array (3) { ["id"] => 4 ["account"] => "a2" ["sum"] => 70 // a1 + a2 + a3 [1] => array (3) { ["id"] => 6 ["account"] => "a4" ["sum"] => 30 // a1 + a4 }
I think I need to use some kind of recursion in my while statement, but I am a little confused. Can you help me?
thank
+3
source to share
3 answers
I found a function that gets results as needed, without recursive queries.
$nodeList = array();
$tree = array();
$query = mysql_query("SELECT A.guid, A.name, A.parent_guid, SUM( S.value_num ) /100 AS suma FROM accounts AS A
LEFT JOIN splits AS S ON S.account_guid = A.guid GROUP BY A.guid ORDER BY A.name");
while($row = mysql_fetch_assoc($query))
$nodeList[$row['guid']] = array_merge($row, array('children' => array()));
mysql_free_result($query);
foreach ($nodeList as $nodeId => &$node) {
if (!$node['parent_guid'] || !array_key_exists($node['parent_guid'], $nodeList))
$tree[] = &$node;
else
$nodeList[$node['parent_guid']]['children'][] = &$node;
}
unset($node);
unset($nodeList);
0
source to share