PHP Language - Re: Recursive array from id/parent

Author

Re: Recursive array from id/parent_id relationship in MySQL result

Zilla

2005-12-15, 6:57 pm

I don't want to get into the discussion the other guys are having.
Instead I have made a solution which gives you exactly what you
requested. Nothing more, nothing less. Here goes:

<?php
//Connect to mysql. Remember to change host, user, pass, db and tbl_name.
$link = mysql_connect("mysql_host", "mysql_user", "mysql_pass");
mysql_select_db("mysql_db");
$query = "SELECT * FROM tbl_name";
$result = mysql_query($query);
$i = 0;
//Store ids, uris, names and parent ids in arrays for later use.
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$ids[$i] = $row['id'];
$uris[$i] = $row['uri'];
$names[$i] = $row['name'];
$parent_ids[$i] = $row['parent_id'];
$i++;
}

//Make pairs of ids and parent ids.
foreach($ids as $key => $value) {
$array[] = $parent_ids[$key] . " " . $value;
}

//Make flat tree structure.
function make_flat_tree($array) {
$test = 0;
foreach($array as $key => $value) {
$used = 0;
foreach($array as $key2 => $value2){
if($key != $key2) {
$explode = explode(" ", $value);
$explode2 = explode(" ", $value2);
$count = count($explode) - 1;
$last = count($explode2) - 1;
$count2 = count($explode2) - 2;
if($explode[$count] == $explode2[$count2]) {
$flattree[] = $value . " " . $explode2[$last];
$test = 1;
$used = 1;
unset($array[$key2]);
}
}
}
if($used == 1) {
unset($array[$key]);
}
}
if(count($array) > 0) {
foreach($array as $value) {
$flattree[] = $value;
}
}
if($test == 1) {
$flattree = make_flat_tree($flattree);
}
return $flattree;
}
$flattree = make_flat_tree($array);

//Remove leading zeros.
foreach($flattree as $value) {
$flattree2[] = substr($value, 2);
}

//Build tree structure with only ids.
function build_tree($array) {
foreach($array as $value) {
$structure = "\$tree";
$explode = explode(" ", $value);
foreach($explode as $value2) {
$structure = $structure . "[" . $value2 . "]";
}
$structure = $structure . " = 0;";
eval($structure);
}
return $tree;
}
$tree = build_tree($flattree2);
ksort($tree);

//Make arrays containing forum details.
foreach($ids as $key => $value) {
$forums[] = array("id" => $ids[$key], "uri" => $uris[$key],
"name" => $names[$key], "parent_id" => $parent_ids[$key] , "sub_forums"
=> array());
}

//Build final tree structure by joining information from $tree and $forums.
function build_full_tree($tree, $forums) {
foreach($tree as $key => $value) {
if(is_array($value)) {
foreach($forums as $value2) {
if($value2['id'] == $key) {
$value2['sub_forums'] =
build_full_tree($value, $forums);
$tree2[] = $value2;
}
}
} else {
foreach($forums as $value3) {
if($value3['id'] == $key) {
$tree2[] = $value3;
}
}
}
}
return $tree2;
}
$tree2 = build_full_tree($tree, $forums);
?>

$tree2 now contains exactly the array you provided.

Zilla.

Stefan Rybacki

2005-12-15, 6:57 pm

Zilla wrote:
> ...

Here is another solution:

<?php

//creates a node array
function make_node($id,$uri,$name,$parent) {
return
array('id'=>$id,'uri'=>$uri,'name'=>$name,'parent_id'=>$parent,'sub_forums'=>array());
}

//target array
$root=array();

//helps us to access items fast
$helptable=array();

//insert root node
$helptable[0]=array('sub_forums'=>&$root);

//get items
$con=mysql_connect("localhost","root","");
mysql_select_db("test");

$res=mysql_query("SELECT * FROM testtable");

//first collect all items
while($row=mysql_fetch_assoc($res)){
//insert item into help table
$helptable[$row['id']]=make_node($row['i
d'],$row['uri'],$row['name'],$row['paren
t_id']);
} // while

//now run thru all items and build the tree array (just means add childs to
parents)
foreach ($helptable as $k=>$v) {
if ($k!=0) {

$helptable[$helptable[$k]['parent_id']][
'sub_forums'][count($helptable[$helptabl
e[$k]['parent_id']]['sub_forums'])]=&$helptable[$k];
}
}

unset($helptable);
//now $root contains the desired tree
print_r($root);

mysql_free_result($res);
mysql_close($con);

?>

Regards
Stefan

Stefan Rybacki

2005-12-15, 6:57 pm

Hilarion wrote:
>
>
> Stefan Rybacki <stefan.rybacki@gmx.net> wrote:
>
>
>
> :) Actually I also wrote my solution before posting first post in this
> topic,
> but I decided not to post it when I saw your (Stefan) post reading:
>

me too, but after reading Zillas post I thought of presenting another, maybe
more straight forward, solution can't be wrong ;)

>
>
> This was exact description of my solution.
>
> Your script looks almost like mine (differences in some details, which
> I described below and in names of variables).
>

Ok, go ahead.

>
>
>
> You do not really need that function because the result from the DB is
> almost
> exactly like what you need.

Correct, I did it because I thought an extraction of this task can't be wrong
since the OP may wants to rethink this kind of storage in a node however its not
really needed yet.

>
>
>
>
> In my solution I created $root (under different name)
> at the end of the script.

yeah. Its equal.

>
>
>
>
> I've done it like this:
>
> $helptable[ $row['id'] ] = $row;
> $helptable[ $row['id'] ][ 'sub_forums' ] = array();
>

yes thats fine.

> and it also could be done like this:
>
> $row[ 'sub_forums' ] = array();
> $helptable[ $row['id'] ] = $row;
>

sure.

>
>
>
> I made it a bit shorter like this:
>
> $helptable[$v['parent_id']]['sub_forums'
][] = & $helptable[$k];
>

yes thats even better and more readable, my first solution worked with
array_push but array_push didn't take references of variables so I came up with
the line above with missing the obvious ;)

>
>
>
> Here I created my $root:
>
> $root = $helptable[0]['sub_forums'];
>

yep almost the same.

>
>

Well done, Hilarion ;)

>
>
> Hilarion

Regards
Stefan

Sponsored Links