Tree (Parent / Child) Methods?

[xerodefect]

Hello. I was wondering which would be the best method of doing a parent / child listing.

 

Ive seen a few different ways in my coding time. One was to use a function that will use a query each time. I would like to do it with one query. (I got one query to work a very long time ago, but it got lost over time). Ive seen the left and right way. Im not a big fan on that one.

 

So at the moment im trying to work with this type of database setup

 

ID | parentID | title

 

Anyone got any suggestions or how I should attempt them?

 

Thanks,

Preston

 

*note - Can be more then one child =)

[Mark Baker]

$items = array(
        array('id' => '1', 'pid' => '0'),
        array('id' => '2', 'pid' => '0'),
        array('id' => '3', 'pid' => '0'),
        array('id' => '4', 'pid' => '1'),
        array('id' => '5', 'pid' => '1'),
        array('id' => '6', 'pid' => '1'),
        array('id' => '7', 'pid' => '2'),
        array('id' => '8', 'pid' => '4')
);

function showLevel($items,$parent) {
    $ulSet = False;
    foreach ($items as $item) {
        if ($item['pid'] == $parent) {
            if (!$ulSet) {
                $ulSet = True;
                echo '<ul>';
            }
            echo '<li>'.$item['id'].'</li>';
            showLevel($items,$item['id']);
        }
    }
    if ($ulSet) { echo '</ul>'; }
}

showLevel($items,'0');

 

[xerodefect]

$items = array(
        array('id' => '1', 'pid' => '0'),
        array('id' => '2', 'pid' => '0'),
        array('id' => '3', 'pid' => '0'),
        array('id' => '4', 'pid' => '1'),
        array('id' => '5', 'pid' => '1'),
        array('id' => '6', 'pid' => '1'),
        array('id' => '7', 'pid' => '2'),
        array('id' => '8', 'pid' => '4')
);

function showLevel($items,$parent) {
    $ulSet = False;
    foreach ($items as $item) {
        if ($item['pid'] == $parent) {
            if (!$ulSet) {
                $ulSet = True;
                echo '<ul>';
            }
            echo '<li>'.$item['id'].'</li>';
            showLevel($items,$item['id']);
        }
    }
    if ($ulSet) { echo '</ul>'; }
}

showLevel($items,'0');

 

 

 

Cool but may I ask what would be your example mysql query to that?

[Mark Baker]

SELECT ID AS id
       parentID AS pid
  FROM table
[code]