NYCPHP Meetup

[Hans Zaunere]

--- Jim Hendricks <jim at bizcomputinginc.com> wrote:
> I know this is a SQL question, but since most apps deal with SQL, I 
> figured I'ld ask here since the app is using PHP and MySQL.
> 
> I have a table that establishes an entity that may have a parent from
> the same table.  I would like to display the data in a tree format
> where all items with a ParentID of 0 ( no parent ) are listed with
> children indented under the parent.

As pointed out Oracle has this with the CONNECT TO method.  Alas, this
is one of the biggest misses in MySQL (IMO).

> The nesting can be infinite, but realistically will only be 2 or 3
> levels deep.  I can see how to do this through a whole series of
> queries, but can see how the performance of such a design could be
> very poor if there is a lot of items.

Sure, multiple queries would do it, and depending on the environment
may be the best way.  I've had to tackle similar problems, and found
that a single dynamically generated SQL query is often best.  If you
form the joins properly (LEFT JOIN table ON something=somethingelse)
you can match much of this functionality (I even use it for subselects,
and have only ran into a wall once).

In a for loop, bascially just join the table to itself (or to another
for that matter) with an incrementing aliased name and then set the
criteria at each level in the WHERE clause.  This is all Oracle's
CONNECT TO is doing behind the scenes - you can even maintain a counter
so you know how far each row is "indented."

Here's some code totally ripped from context that probably won't help
at all:

for( $i = 1; $i < count($keys); ++$i) {
   $tag = mysql_escape_string($keys[$i]);
   $select .= ",m$i.mid,m$i.R_mid,m$i.file,m$i.tag,m$i.title";
   $from .= " LEFT JOIN psite.map m$i ON m$i.R_mid=m".($i-1).".mid";
   $where .= " AND m$i.tag='$tag'";
}

$result = mysql_query("SELECT $select FROM $from WHERE $where", $DB);

H

=====
Hans Zaunere
New York PHP
http://nyphp.org
hans at nyphp.org

__________________________________________________
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com