Jump to content

Mysql Hierarchy Structure


jazzman1

Recommended Posts

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 by jazzman1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

@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 by jazzman1
Link to comment
Share on other sites

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

 

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);
}

Link to comment
Share on other sites

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 by PFMaBiSmAd
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.