I have a table with data stored hierarchicaly using 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 a table where I keep values for these accounts
id_account value 2 10 2 10 4 20 5 30 6 10
I made a function which returns all child account given a 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'm trying to do is a function which returns not only children accounts but also the sum of all values including children of every result. For example
getChildrenAccount(4)
Would 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 have to use some kind of recursion in my while statement but I'm a little bit confused. Can you help me please?
Thanks
I found a function which 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);
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) ';
....
}
You need to iterate over your result, then for each id call getChildrenAccount for its parent id.