Query problem expressed in PHP

[Lars Eighner]

I have this MySQL table:

TABLE: content_path
+--------------+--------+
| node | parent |
+--------------+--------+
| /works | / |
| /EIGHNER | / |
| / | 0 |
| /works/essay | /works |
+--------------+--------+

The table will never be very large, so recursion seems to be the best way to
traverse this hierarchal information. Obviously these are unix-style file
paths, but in researching this, I see the same sort of table often occurs in
product category problems. I also note that such tables don't comply with
database normalization --- which is over my head --- but many people are
attracted by the convenience of this kind of table.

I wrote (stole[1]) this function, which works.

<?php

function tree_slash_children(){
global $line;
$numargs = func_num_args();
if($numargs >= 1){$field = func_get_arg(0);}else{$field = 'node';}
if($numargs >= 2){$table = func_get_arg(1);}else{$table = 'content_path';}
if($numargs >= 3){$parent = func_get_arg(2);}else{$parent = '0';}
if($numargs >= 4){$level = func_get_arg(3);}else{$level = 0;}
$result = mysql_query('SELECT node FROM '.$table.
' WHERE parent="'.$parent.'"');
while ($row = mysql_fetch_array($result)) {
$line = $line . str_repeat('|',$level).$row['node'];
tree_slash_children($field, $table, $row['node'], $level+1);
}

}

?>

Because global $line = '' before the call, the return is:

/|/works||/works/essay|/EIGHNER



Now it occurs to me that the parent field is redundant.

If node = '/' we know there is no parent, and otherwise we know the parent
is s#/[^/]*$##.

I'd like to dispose of the parent field, but MySQL despite having a replace
function and having regex matching, does not have regex replacement.

I'd like to do something like:

function tree_slash_descend(){
global $line;
$numargs = func_num_args();
if($numargs >= 1){$field = func_get_arg(0);}else{$field = 'node';}
if($numargs >= 2){$table = func_get_arg(1);}else{$table = 'content_path';}
if($numargs >= 3){$parent = func_get_arg(2);}else{$parent = '';} //?
if($numargs >= 4){$level = func_get_arg(3);}else{$level = 0;}
$result = mysql_query('SELECT node FROM '.$table.
' WHERE [****some kind of test on node *****]');
while ($row = mysql_fetch_array($result)) {
$line = $line . str_repeat('|',$level).$row['node'];
tree_slash_children($field, $table, $row['node'], $level+1);
}

}

but I don't see it.

Any suggestions?


[1] based on
<http://www.sitepoint.com/article/hierarchical-data-database/>
Storing Hierarchical Data in a Database [PHP &amp; MySQL Tutorials]
by Gijs Van Tulder

 

[Lars Eighner]

In our last episode,
the lovely and talented Lars Eighner
broadcast on comp.lang.perl.misc:

I have this MySQL table:

Sorry. Wrong group.