I have data stored in the following method:
id, father_id, level, order & data.

What I'm trying to do is to display the data in a nested list, like this:

<ul>
<li>aa</li>
<li>ab
<ul>
<li>aba</li>
<li>abb</li>
</ul>
</li>
<li>ac
<ul>
<li>aca</li>
<li>acb</li>
<li>acc</li>
</ul>
</li>
<li>ad</li>
</ul>
But no matter what, I can't think of a way to accomplish this. I think the best way I can order the data inside the SELECT is like this:

order by `type` asc, `level` asc, `order` asc
And then maybe move the recordset into an array?

It's not possible to use the order clause to get data from that table structure in the correct order in a general (abritrarily deep way). You don't say what the 'type' column is, but if it's something like a 'branch id' it can help you achieve the desired layout for a maximum of the depth shown (root nodes +1 deep). Ordering by level or parent_id only gets you so far, structurally, and you'll find this falls apart if you add a child to 'aba' for instance, because it can't reconcile its position with its siblings with the available structural data.

So assuming type is some kind of branch id and that the table therefore looks like this: ('aa%' is branch 1, 'ab%' is 2, etc)

$sth = $dbh->query('SELECT level, data FROM t ORDER BY type, father_id, ordering');
$last_level = null;
echo '<ul>';
while ($row = $sth->fetch_assoc())
{
if (!is_null($last_level))
{
if ($row['level'] > $last_level) # Up a level, open a new list
echo '<ul>';
else if ($row['level'] < $last_level) # Down a level or two, close the last list item and as many parents as applicable
echo '</li>' . str_repeat('</ul></li>', $last_level - $row['level']);
else
echo '</li>'; # No level changes, just close the last list item
}

If you need your structure to get any deeper you're going to have to bite some kind of bullet, either refactoring to a non-recursive structural notation like MPTT, or actually doing recursive queries on a table structured similarly to this one to get things in the proper order. The former is a pain with race conditions and the latter doesn't scale.

b_hole

06-07-2007, 09:02 AM

My bad, the `type` field shouldn't be there. Therefore, the table should look like this:

Sorry about the misleading. BTW, I can't change the structure of this table.
And yes, I also need to be able to go deeper. What I was thinking was to first select all the data, and then copy it to an array, and then do a recursive function on the array, without making so many SELECT statements.
What do you think?

dumpfi, I've been using your code for a while now, but it has a big problem: the data isn't ordered correctly, using the `ordering` column. Your function order the data using `id` column, and the data isn't ordered the right order.
Changing ORDER BY id ASC into ORDER BY ordering ASC isn't the solution: it displays only the part of the data. Why?

Thanks again.

b_hole

06-15-2008, 10:34 AM

Hi,

I'm bumping this up, as I have a problem with this function: as you can see, it orders the data according to the ID field. The problem occurs after editing the table: if I want to change the table to look like this:

<ul>
<li>aa</li>
<li>ab
<ul>
<li>aba</li>
<li>abb</li>
</ul>
</li>
<li>acc
<ul>
<li>ac
<ul>
<li>aca</li>
<li>acb</li>
</ul>
</li>
</ul>
</li>
<li>ad</li>
</ul>
It wouldn't display all of the data, because it orders the table by ID. As I said, this problem can happen only if the data is re-ordered (editing father_id).

Can someone please help me with this this? I can't figure out how to fix this...