This is not as hard as it may seem..
Personal note:
You query looks pretty screwed up.. or at least very messy.. It might be a good idea to clean up queries which look to complex.. often, complex queries are the result of a bad structure in the database..
First, I'd suggest that you add a few columns to you database-table. As a general rule when creating tables is to have a field with a counter, that creates a unique ID for each row. This row is preferably set as index and auto_increment (don't know if that's the right terminology for other databases than MySQL..).
Create a table which looks like this
[FONT=courier new] id | parent_id | text | type [/FONT]
It's always better to store binary numbers in tables than it is storing strings which you supposed to match to later in the code. The risk of typos and such is minimized, and comparison of two integers is faster than the comparing of two string..
If you populate the the table above with your data (converted to the new table) you'll probably get the following result back:
+----+-----------+------------------------+------+
| id | parent_id | text | type |
+----+-----------+------------------------+------+
| 1 | null | [url]http://www.ee/[/url] | 0 |
| 2 | null | SUBFOLDER | 1 |
| 3 | null | [url]http://www.google.com/[/url] | 0 |
| 4 | 2 | [url]http://www.amazon.com/[/url] | 0 |
+----+-----------+------------------------+------+
Now it's up to some recursive programming to create the tree.
Create a function, that get all rows in the tree based on their parent_id:
function createNode ($id = "") {
if (isset($id) && $id != "") {
/* Connect to the database */
$db = mysql_connect('localhost', 'user', 'password') || die "Error.\n";
mysql_select_db('database');
/* Get the data */
$q = "SELECT * FROM the_table WHERE id = '$id';";
$r = mysql_query($q, $db);
/* check if anything was returned */
if (mysql_num_rows($r) > 0) {
/* Add a handler for various types you want (based on the type-field
* in the table */
/* print the row's text */
echo mysql_result($r, 0, 'text') ."\n";
/* now get all the children (parent_id = $id) */
$q = "SELECT * FROM the_table WHERE parent_id='$id';";
$r = mysql_query($q, $db);
/* loop through all returned rows (all the current children */
while ($child = mysql_fetch_row($r)) {
/* for each child, call this function */
createNode($child[0]);
}
}
mysql_close($db)
}
}
/*
* Here we connect to the database, get all rows with the parent_id we're
* looking for (in the case of creating the whole tree, parent_id should
* be null
*/
/* connect to the database */
$db = mysql_connect('localhost', 'user', 'password') || die ('Error.');
mysql_select_db('database', $db);
/* get the rows */
$q = "SELECT * FROM the_table WHERE parent_id=null;";
$r = mysql_query($q);
/* loop through the result */
while ($row = mysql_fetch_row($r)) {
/* for each node, call the createNode-function with the current ID */
createNode($row[0]);
}
I haven't tested the code above, but it ought work.. I wrote off the top of my head, so tell me if it doesn't work, and we'll probably be able to make it work.. 🙂
The way this works is that it loops through all the "root"-nodes, checks for children and if there is any, it loops through them in the same way, checks for children, if any loop through the and on and on..
Hope you get the picture!
Good luck,
Olle
