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
Jorge zapata
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
Jorge zapata
source
to share
function getChildrenAccount($accountID){
$query = ' SELECT id,account,sum(value) as `SUM`
FROM accounts,accountsValues
WHERE accounts.id = accountsValues.id_accounts
AND id = $accountID
OR id IN (SELECT id FROM accounts where parent = $accountID) ';
....
}
0
amd
source
to share
You need to iterate over your result, then for each id call getChildrenAccount for its parent id.
0
Aerik
source
to share