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


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


source


You need to iterate over your result, then for each id call getChildrenAccount for its parent id.

0


source







All Articles