Sunday, 2 June 2013

MYSQL - multi-dimensional array - loop or recursion?

MYSQL - multi-dimensional array - loop or recursion?

In my MYSQL database, I have 2 tables:
+-------------------+  +---------------------------------+
|       tags        |  |          relationships          |
+----+--------------+  +---------+---------+------+------+
| id | tag          |  | x_table | y_table | x_id | y_id |
+----+--------------+  +---------+---------+------+------+
| 1  | parent 1     |  | tags    | tags    | 1    | 2    |
| 2  | child 1      |  | tags    | tags    | 1    | 3    |
| 3  | child 2      |  | tags    | tags    | 3    | 4    |
| 4  | grandchild 1 |  +---------+---------+------+------+
+----+--------------+
I need to load these relationships into a multi-dimensional array. The number of children can change and the depth can change. The depth is also different for each relationship tree.
Now I've read that recursive functions are slower than loops for interpreted languages like PHP, and was wondering if it would even be possible to write this with loops, and if so, if it would be faster and less resource intensive than using a recursive function?
The structure of the multi-dimensional array should be as follows:
array(
    'id' => $id_from_db,
    'tag' => $tag_from_db,
    'children' => array( /* Just like parent array or false/NULL */ )
)
EDIT: SQLfiddle here

No comments:

Post a Comment