Mysql Hierarchy Structure

[jazzman1]

Hi friends,

I have a database named 'abv' with a table named: category -

 

+-------------+-------------+------+-----+---------+----------------+
| Field	 | Type	 | Null | Key | Default | Extra		 |
+-------------+-------------+------+-----+---------+----------------+
| category_id | int(11)	 | NO | PRI | NULL | auto_increment |
| name	 | varchar(20) | NO |	 | NULL |			 |
| number	 | int(11)	 | NO |	 | NULL |			 |
| parent	 | int(11)	 | YES |	 | NULL |			 |
+-------------+-------------+------+-----+---------+----------------+

+-------------+----------+--------+--------+
| category_id | name	 | number | parent |
+-------------+----------+--------+--------+
|		 1 | Europe |	 0 | NULL |
|		 2 | BG	 |	 8 |	 1 |
|		 3 | Sofia |	 5 |	 2 |
|		 4 | Varna |	 2 |	 2 |
|		 5 | Mladost |	 4 |	 3 |
|		 6 | Chaika |	 9 |	 4 |
+-------------+----------+--------+--------+

 

I used Barand's code to display its hierarchy:

 

<?php

//connect to database

$link = mysql_connect('localhost','root','');

mysql_select_db('abv');

$sql = "SELECT category_id, name, parent, number
 FROM category";

$res = mysql_query($sql);

while (list($id, $name, $parent, $number) = mysql_fetch_row($res)) {
$data[$parent][] = array('id'=>$id, 'name'=>$name, 'number'=>$number);
}

// function to print a category then its child categories
function displayHierarchy(&$arr, $parent, $indent=0)
{
$ind = $indent * 30;
if (isset($arr[$parent]))
foreach($arr[$parent] as $rec)
{
 echo "<div style='width:300px; margin-top:5px; margin-left: {$ind}px; padding:5px; border:1px solid gray;'>
 {$rec['name']} - {$rec['number']}
 </div>" ;
 displayHierarchy($arr, $rec['id'], $indent+1);
}

}

// call the recursive function
displayHierarchy($data, null);

 

Results:

Europe - 0

BG - 8

Sofia - 5

Mladost - 4

Varna -2

Chaika - 9

 

My question is, how to get this data displaying a result like this:

 

Europe - 26

BG - 26

Sofia - 9

Mladost - 4

Varna - 11

Chaika - 9

 

Thanks in advance

Edited December 10, 2012 by jazzman1

[Muddy_Funster]

it would help (more than a little) if you gave some relevence to the second number set you want to get.

[Christian F.]

I think he meant for the values to the the sum of the children elements + its own number, but forgot to add a 2 in the results for EU and BG.

[jazzman1]

I think he meant for the values to the the sum of the children elements + its own number, but forgot to add a 2 in the results for EU and BG.

 

Sorry guys, for EU and BG the result has to be - 28.

 

Yes Christian, I want to take values as the sum of the children's number + its own.

 

Examples:

EU - 0+8+5+2+4+9

BG -8+5+2+4+9

Sofia-5+4 (Sofia+Mladost)

Varna-2+9 (Varna+Chaika)

 

Mladost and Chaika don't have chindren.

[Christian F.]

OK, it seems it was my math that was off. Sorry about that.

[Muddy_Funster]

and are you wantiing this done in the SQL or with the PHP?

[jazzman1]

and are you wantiing this done in the SQL or with the PHP?

it really doesn't matter!

[PFMaBiSmAd]

You would need to iterate over the data to produce the totals, then display the result -

 

<?php

// sample data -
$data[null][1] = array('name'=>'Europe', 'number'=>0,'total'=>0);
$data[1][2] = array('name'=>'BG', 'number'=>8,'total'=>0);
$data[2][3] = array('name'=>'Sofia', 'number'=>5,'total'=>0);
$data[2][4] = array('name'=>'Varna', 'number'=>2,'total'=>0);
$data[3][5] = array('name'=>'Mladost', 'number'=>4,'total'=>0);
$data[4][6] = array('name'=>'Chaika', 'number'=>9,'total'=>0);

// additional test values -
/*
$data[2][7] = array('id'=>7, 'name'=>'abc', 'number'=>1,'total'=>0);
$data[1][8] = array('id'=>8, 'name'=>'def', 'number'=>3,'total'=>0);
$data[8][9] = array('id'=>9, 'name'=>'defg', 'number'=>5,'total'=>0);
$data[null][10] = array('id'=>10, 'name'=>'ghi', 'number'=>0,'total'=>0);
$data[10][11] = array('id'=>11, 'name'=>'ghijlk', 'number'=>33,'total'=>0);
*/

// this function produces the totals only
function totalHierarchy(&$arr, $parent, $bc=''){
   if (isset($arr[$parent]))
       foreach($arr[$parent] as $id=>$rec){

           // form a bread-crumb to reference the total elements
           $p = is_null($parent) ? 'n':$parent; // convert the null parent to a value that can be seen for debugging
           $bread = ($bc != '') ? '|' : ''; // make bread-crumb (the incoming $bc value is not altered so it has the correct value for the duration of the loop at any recursion level)
           $bread .="{$p}_{$id}"; // form bread-crumb for the current recursion

           // add number to appropriate totals -
           $parts = explode('|',$bc.$bread); // the existing bread-crumb + bread-crumb for the current recursion
           foreach($parts as $crumb){
               list($indexa,$indexb) = explode('_',$crumb);
               $indexa = $indexa == 'n' ? null : $indexa; // convert the 'n' back to null
               $arr[$indexa][$indexb]['total'] += $rec['number'];
           }
           totalHierarchy($arr, $id, $bc.$bread);
       }
}

// this function displays the information
function displayHierarchy(&$arr, $parent, $indent=0){
   $ind = $indent * 30;
   if (isset($arr[$parent]))
       foreach($arr[$parent] as $id=>$rec){

           echo "<div style='width:300px; margin-top:5px; margin-left: {$ind}px; padding:5px; border:1px solid gray;'>
           {$rec['name']} - {$rec['total']}
           </div>" ;

           displayHierarchy($arr, $id, $indent+1);
       }
}

// call the recursive total function
totalHierarchy($data, null);

// call the recursive display function
displayHierarchy($data, null);

[jazzman1]

Thank you PFMaBiSmAd, I will try this

[jazzman1]

@PFMaBiSmAd, I don't wanna bother you, but my data comming from DB looks exactly like this:

 

$data[null][0] = array('name'=>'Europe', 'number'=>0,'id'=>1);
$data[1][0] = array('name'=>'BG', 'number'=>8,'id'=>2);
$data[2][0] = array('name'=>'Sofia', 'number'=>5,'id'=>3);
$data[2][0] = array('name'=>'Varna', 'number'=>2,'id'=>4);
$data[3][0] = array('name'=>'Mladost', 'number'=>4,'id'=>5);
$data[4][0] = array('name'=>'Chaika', 'number'=>9,'id'=>6);

 

Is it possible to get the same result like yours ?

 

When you have a time, please take a look at this.

 

Thank you,

jazz.

Edited December 10, 2012 by jazzman1

[PFMaBiSmAd]

Change -

$data[$parent][] = array('id'=>$id, 'name'=>$name, 'number'=>$number);

 

To -

$data[$parent][$id] = array('name'=>$name, 'number'=>$number,'total'=>0);

[jazzman1]

Hm...it's getting more and more complicate for me

 

If I'm using the code below, Barand's function failed, because it needs this id to be a part of an array:

 

displayHierarchy($arr, [b]$rec['id'][/b], $indent+1);

while (list($id, $name, $parent, $number) = mysql_fetch_row($res)) {
$data[$parent][$id] = array('name'=>$name, 'number'=>$number,'total'=>0);
}

 

If I try to change it like that, the old result is displaying:

 

while (list($id, $name, $parent, $number) = mysql_fetch_row($res)) {
$data[$parent][$id] = array('id'=>$id,'name'=>$name, 'number'=>$number,'total'=>0);
}

[PFMaBiSmAd]

If you look at the code I posted, you will see that I modified the code in his function to use the modified data structure.

 

In fact, the code I posted is a working example. Put it into a .php file and play with it to see what the code is doing.

Edited December 11, 2012 by PFMaBiSmAd

[jazzman1]

Wow....... fabulous

 

Sorry for that, it woks perfectly for me...... thanks again my friend!

 

EDIT: I have to spend a time learning more about recursive functions in php.

Edited December 11, 2012 by jazzman1