Threading articles from a SQL DB using PHP

[BN]

I have a table with (for brevity) 2 columns. RecID and ParentID.

All absolute or generation 0 PARENT records will have a ParentID of 0
(zero) but their children will have their parentID pointed to an
existing parent's RecID (a parent of any generation).

I want to list out the parents with all their children
and grandchildren under them and then start listing the next parent and
all the children and grand children under them etc.

example

RECID ParentID
PARENT 1 0
child 2 1 child of p1
child 6 1 child of p1
child 4 6 child of child
child 5 4 child of child of child
PARENT 3 0 next parent of gen 0
child 7 3 child of p3 or RecID 3


etc.. I want to list it in that order.

When I select it of the PostgreSQL database, I get records but not in
the above order. I have to load it into a temporary array and then put
it into that threaded ordered. I am using PEAR:DB for access to the
PGSQL database.

How do I do this? Arrays? Maps?

Can elements in maps be listed through iteration?

Are there processor efficient ways to do this?

Any help would be appreciated!!

[Martyr2]

Re: Threading articles from a SQL DB using PHP

I had come across a similiar exercise in a C++ class I had in college.
What you are describing is a binary tree (except in this case it has
more than just two children), where the branches show generations. I
have to tell you the methodology in C++ and then you can apply it in
PHP. We solved this through the use of a linked list with a set of
pointers and recursion. Each object points to its children and while
the pointers are not null, it calls the childs method and starts the
process all over again until it reaches the end. When it hits null, it
climbs up and down into the next child. When children are exhausted for
a given parent, it calls its parent etc back up the tree. I have found
an article which shows the ideas, problems, and a few code solutions.

http://cslibrary.stanford.edu/110/BinaryTrees.html

I hope this helps and makes sense.

[Hilarion]

Re: Threading articles from a SQL DB using PHP

> I have a table with (for brevity) 2 columns. RecID and ParentID.[color=blue]
>
> All absolute or generation 0 PARENT records will have a ParentID of 0
> (zero) but their children will have their parentID pointed to an
> existing parent's RecID (a parent of any generation).
>
> I want to list out the parents with all their children
> and grandchildren under them and then start listing the next parent and
> all the children and grand children under them etc.
>
> example
>
> RECID ParentID
> PARENT 1 0
> child 2 1 child of p1
> child 6 1 child of p1
> child 4 6 child of child
> child 5 4 child of child of child
> PARENT 3 0 next parent of gen 0
> child 7 3 child of p3 or RecID 3
>
>
> etc.. I want to list it in that order.
>
> When I select it of the PostgreSQL database, I get records but not in
> the above order. I have to load it into a temporary array and then put
> it into that threaded ordered. I am using PEAR:DB for access to the
> PGSQL database.
>
> How do I do this? Arrays? Maps?
>
> Can elements in maps be listed through iteration?
>
> Are there processor efficient ways to do this?[/color]


If it'd be Oracle, then you could use hierarchical query, but PostgreSQL
does not support it (AFAIK).

You could do it with recursive function retrieving children of
a specified parent (which means multiple selects on database):

<?php

function get_children( $connection, $callback_funct ion, $parentid = 0 )
{
$qry = 'SELECT recid, parentid, name '
. 'FROM my_table '
. 'WHERE parentid = ' . $parentid . ' '
. 'ORDER BY recid '
;
$data = query_the_datab ase_in_some_way ( $connection, $qry );
if ($data===FALSE)
{
// Throw exception or issue warning and:
return FALSE;
}
foreach( $data as $row )
{
call_user_func( $callback_funct ion, $row );
if (!get_children( $connection, $callback_funct ion, $row['recid'] ))
return false;
}
return TRUE;
}

$conn = establish_datab ase_connection_ in_some_way();

function print_results( $row )
{
echo "<tr>\n";
foreach( $row as $cell )
echo '<td>' . htmlspecialchar s( $cell ) . "</td>\n";
echo "</tr>\n";
}

echo "<table>\n" ;
get_children( $conn, 'print_results' );
echo "</table>\n";

?>


Hilarion