[SOLVED] Converting database hierarchy into Array/XML (adjacency model)

[HorseFace]

Hi Everyone,

 

I need some help transforming a database table into an XML or Array. I am using an adjacency model for my database, meaning i have a structure similar to the following:

 

Project Id      Parent Id

 

1                null

2                1

3                1

4                2

5                3

6                5

 

From that hierarchy i wish to get something like this:

 

<Projects>

  <Project id="1">

      <Project id="2">

        <Project id="4"/>

      </Project>

      <Project id="3">

        <Project id="5">

            <Project id="6"/>

        </Project>

      </Project>

  </Project>

</Projects>

 

which i will then use to feed a Tree Component in my UI (made in Flash). What would be

necessary would be some kind of recursive function to create this XML/Array

I'm guessing. I am open to suggestions including changing the database model (for example using a right/left node model instead of adjacency). The nodes will constantly be changed (like a file system) so i will need to take the model's performance during editing into consideration. I am using a mySQL database and PHP5. I would really appreciate any help/tips/ideas that anyone might have in this area.

 

Thanks in advance

 

/Eric

 

[Fyorl]

That looks like it should be possible. I'll need a bit of clarification. Do you have one table containing the relationship (like the example you posted) and then a separate table with the data rows keyed to an id?

 

If so then it should be a matter of getting the contents of both tables and placing them in a hierarchy based on the relationship data.

 

IMO it would be easier to generate an array than XML but both are fairly easily accomplished with PHP5 so it depends which is more useful. If you're transferring the data to Flash then I would imagine XML would be more useful as I can't see how a (serialized?) PHP array is going to be understood by Flash without writing some ActionScript to parse it.

[Barand]

somethng like this?

<?php
$data = array(
        1 => 0,
        2 => 1,
        3 => 1,
        4 => 2,
        5 => 3,
        6 => 5
);

echo '<?xml version="1.0" encoding="utf-8"?> ';
echo "<projects>\n";
tree(0);
echo "</projects>\n";

function tree ($parent, $level=0)
{
    global $data;
    $children = array_keys($data, $parent);
    if ($children)
    foreach ($children as $kid)
    {
        $indent = str_repeat("\t", $level+1);
        echo "$indent<project id='$kid'>\n";
        tree($kid, $level+1);
        echo "$indent</project>\n";
    }
} 
?>

[HorseFace]

Hi,

 

Thanks for your reply,

 

Fyorl: The data and hierarchy will be placed in the same table. I will feed this information to a Tree component in flex as xml, but formating a php array into an xml is not a problem.

 

Barand: I have previously managed to create an XML using a similar function but later realized that this will only work if the rows in the database are sorted in a specific way (child node always directly after its parent node). The goal is to be able to move parents and child nodes freely (like in windows explorer for example) meaning that a node with id = 2 could become the child of node with id = 23 for example. This would mean that during the foreach loop nodes might need to be skipped temporarelly until their parents are created. One option would be to recreate nodes every time there is a change in the table, but this could be expensive with hundreds of nodes. Now im just babbling, maybe someone has some ideas :-) I guess im trying to recreate windows explorer :-S. Thanks again for your reply

 

/Eric

[Barand]

read the db data into an array like my $data array first. Then you eliminate the problem.

[Fyorl]

You should be able to use the SimpleXML module that's included with PHP5 to produce XML. I think it has some functions that work with arrays, my memory's a bit hazy though.

 

Seems fairly simple to get the XML structure you want once you've got all the rows into an array. Assuming of course that they have an id and parent id field.

[HorseFace]

Thanks so much guys,

 

array_keys did the trick. Really appreciate your help!

 

/Eric