Posted by Mike Placentra II on 10/19/07 20:04

How to get that into an array depends on your situation.

If you don't already have a lot of data in the database, working with
XML files is a much easier way to deal with hierarchies unless of
course there are other needs for a database or individual node access.

If you don't already have a lot of data in the database and will be
putting data in with another algorithm, I would suggest creating
another field in which you can save a string which represents the
node's location in the hierarchy (such as "1|2|3|5|7" for the bottom
right node in your tree example). Then you can explode()/split() the
string to more quickly get it into the appropriate location in the
array. Hopefully the data in the database will be returned in an order
that nodes come after their parent nodes and the root node is first
(hint - sort with your query), and you can iterate through the nodes
in the location string to get to where in your tree array the data
should go. If the database result is not ordered this way, you can
still create upper level nodes from the location string because the
location string says the IDs of the upper level nodes.

If you keep your database table the way you described it, you have to
iterate through every node of the tree array to find the parent node
specified for each row returned from the database. If the database
result is not ordered as described above, you will not be able to
determine the root-relative location in the hierarchy of the current
row if its parents are not all entered already so you will need to
create a "payload" of orphan branches, and constantly see if the
branches can be matched up to any of the newly added nodes or if new
nodes can be added to one of those branches in the payload.

If the last method causes too much trouble and you'd like to switch to
XML files or the first mentioned database table layout, you can create
the latter algorithm to make an array and then write it back to a
database or file with yet another algorithm.

What works for you will also depend on how large the hierarchy is and
how much data is in it.

-Michael Placentra II


On Oct 19, 3:06 pm, uidzer0 <ben.lemasur...@gmail.com> wrote:
> Hey everyone,
>
> I'm having a little trouble writing an algorithm to create a
> hierarchal structure from a database
>
> I would like to put this into an array
>
> Can anyone point me in the right direction? Let me know if this isn't
> clear - I can try to elaborate.
>
> Thanks!
> Ben

[Back to original message]