Converting database hierarchy into Array/XML-adjacency model
Posted: Sun Jan 13, 2008 1:27 pm
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
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