Converting database hierarchy into Array/XML-adjacency model

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
HorseFace
Forum Newbie
Posts: 2
Joined: Sun Jan 13, 2008 1:23 pm

Converting database hierarchy into Array/XML-adjacency model

Post by 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Converting database hierarchy into Array/XML-adjacency model

Post by Weirdan »

If edits are going to be frequent, you're basically fine with adjacency model. Yes, recursive retrieval would be necessary, something like this:

Code: Select all

 
/**
 * @return string
 */
function getTree($parent = false) {
   global $db;
   if (!($res = mysql_query('select * from table where parent_id' . ($parent === false ? ' is null ' : ' = ' . (int) $parent, $db)))) {
       die(mysql_error($db));
   }
   $ret = '';
   while ($row = mysql_fetch_array($res)) {
       $ret .= '<Project id="' . $row['id'] . '">';
       $ret .= getTree($row['id']);
       $ret .= '</Project>';
   }
   return $ret;
}
// setup database connection here:
// $db = mysql_connect(......);
echo getTree();
 
HorseFace
Forum Newbie
Posts: 2
Joined: Sun Jan 13, 2008 1:23 pm

Re: Converting database hierarchy into Array/XML-adjacency model

Post by HorseFace »

H Weirdan,

Thanks for your reply, 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 be that during the while loop nodes might need to be skipped untill 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
Post Reply