Posted by C. (http://symcbean.blogspot.com/) on 12/05/07 13:11

On 5 Dec, 11:13, Mikhail Kovalev <mikhail_kova...@mail.ru> wrote:
> Hi.
>
> I work with recursive array trees of 10-20 levels in PHP. So far I
> have been using serialize() to store the arrays, generating files 5+
> MB large, which take 10-15 seconds to unserialize and about tenfold of
> RAM! I've been adviced to use MySQL (relational db's), but are there
> any other options beside that and var_export/include? Something that
> works in a similar way as MySQL when adding a new element without
> loading the whole database itself...
>
> Thanks!

Any relational DB can model a hierarchical data structure - at the
simplest level:

node_id unique identifier, not null
parent_node null
payload whatever

But getting the threaded structure is a bit tricky -

SELECT parent.node_id as Parent_node_id, parent.payload as
parent_payload,
child.node_id as child_node_id, child.payload as child_payload
FROM mytable parent, mytable child
WHERE child.parent_node=parent.node_id
AND parent.node_id=$start_here

... i.e. by explicitly declaring aliases for each level in the tree you
can get the sub-nodes, but when you don't know how deep the tree is,
its not possible to return the whole structure in a single query using
standard SQL. Oracle allows you to compose queries returning
representations of hierarchical data (have a google for 'connect by' &
'start with') but thats rather non-standard.

Another solution is to run a single query one the database to return
the entire unconnected tree and link it in PHP...
(for simplicity I'm omitting the payload)..

$src=mysql_query('SELECT * FROM my table');
while ($row[++$x]=mysql_fetch_assoc($src)) {
if (!$row[$x]['parent_id']) {
$final[$row[$x][$node_id]]=array();
}
}
add_children($final, $row);

function add_children(&$final, &$row)
{
foreach($final as $node_id=>$contents) {
if (! is_array($final[$node_id]) $final[$node_id]=array();
foreach($row as $node) {
if ($node['parent_id']==$node_id) {
$final[$node_id][$node['node_id']]=$node;
add_children($final[$node_id][$node['node_id']], $row);
}
}
}
}

(YMMV - not tested)
Although this is far from intuitive and has a very high order of
execution (O^NM?).

The best solution is to look at your system and see if you really need
the entire tree in memory, as a tree at run time - it might be simpler
to run a few queries to descend the tree to the relevant node, or if
you want to return all the nodes under a particular parent then
maintain a corresponding data structure.

You might also consider using the filesystem as the database - since
it is intrinsically an indexed tree structure - so....

$payload=file_get_contents(TREE_ROOT . "/tangibles/organic/vegetable/
fruit/apples/coxs_orange_pippins.txt");

C.

[Back to original message]