Recursive MySQL query?

Trying to SELECT all parent/child rows

[erikcw]

Hi all,

I'm try to create an unordered list of parent/child relationships in my DB.

My DB table is setup like this.
members
-member_id
-fname
-lname
-parrent_id

So I want to query this table and output an unordered list like this:

<ul>
<li>Name of Member 1 (count of children)</li>
<ul><li>one list item</li>
<li>for each member_id with parent_id=1</li>
</ul>
<li>Name of Member 2 (0)</li>
<li>Name of Member 3 (1)</li>
<ul><li>all of member 3's chilren (1)</li>
<ul><li>grandchildren (0)</li></ul>
</ul>
</ul>

I was think maybe some sort of MySQL join would do the trick - but I'm not really sure how to tackle the problem.

Any ideas?

Thanks!
Erik

[deejay]

Do a search for the 'modified preorder tree traversal' method - there's an excellent tutorial at Sitepoint.

As I understand it, recursive queries are pretty heavy duty to run.

The above method gives the same result, but the query isn't recursive.

Basically it's a two-step method. One query to establish the order of the output, which is stored in two fields in your database table - this only needs to be run when you change or add to the structure of the menu.

A second to display the menu.

Works a treat.