Sorting a list by references

Question

I have a table containing (essentially) three columns - id, name, ref_id.

I would like to create an indented list where the columns with ref_id would be indented below the column with the corresponding id, for example:

Name      | ID | Ref ID
about       1    0
story       2    1
history     3    1
contact     4    0
help        5    0
map         6    4
directions  7    4

Would ideally create something like this:

about
- story
- history
contact
- map
- directions
help

What would be ideal is one MySQL query that would return the full list as above, if not something that would create it with the least amount of SQL calls and cpu usage. The only way I can think of doing it is incredibly wasteful and I am sure there is a better way.

Thanks in advance!

Answer 1

--MySQL 5.1 happiness
SELECT
   CASE WHEN tp.Level = 1 THEN tp.Parent
   ELSE CONCAT( '- ', tp.Name)
   END AS result
FROM (

    SELECT
     t.name,
     CASE
        WHEN t.ref_id = 0 THEN t.name
        ELSE t2.name
       END AS Parent,
       CASE
        WHEN t.ref_id = 0 THEN 1
        ELSE 2
       END AS Level
    FROM question_1900097 t
    LEFT JOIN question_1900097 t2 ON t.ref_id = t2.id

    ) AS tp
ORDER BY tp.Parent, tp.Name;

Answer 2

The PHP version

$in = array(
    array('about',1,0),
    array('story',2,1),
    array('history',3,1),
    array('contact',4,0),
    array('help',5,0),
    array('map',6,4),
    array('directions',7,4)
);
foreach ($in as $k => $v) {
    if ($v[2] === 0) { $out[$v[1]][0] = $v; };
    if ($v[2] > 0) { $out[$v[2]][1][] = $v;};
}
foreach ($out as $k => $v) {
    echo $v[0][0] . "\n";
    if (isset($v[1])) {
    foreach ($v[1] as $sk => $sv) {
        echo " - " .$sv[0] . "\n";
    }
    }
}