Sorting Adjacency List Children Alphabetically

Question

If I have an adjacency list sorted by id/parent_id, is there an easy way to sort all of a parent's children alphabetically by a third text field (say "name")?

I have used information provided here: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ to get MySQL to return the sorted adjacency list. Ideally this would allow me to sort the children by a third column, but the test data set does not include extra columns at all in that example.

My data looks like this after I query for it but it needs to be sorted by the descendants, alphabetically.

$myArr[] = array(1,0,"instruments");
$myArr[] = array(2,1,"electric");
$myArr[] = array(3,1,"acoustic");
$myArr[] = array(4,2,"guitar");
$myArr[] = array(5,2,"banjo");
$myArr[] = array(6,3,"guitar");
$myArr[] = array(5,3,"banjo");

Or:

array (
  0 => 
  array (
    0 => 1,
    1 => 0,
    2 => 'instruments',
  ),
  1 => 
  array (
    0 => 2,
    1 => 1,
    2 => 'electric',
  ),
  2 => 
  array (
    0 => 3,
    1 => 1,
    2 => 'acoustic',
  ),
  3 => 
  array (
    0 => 4,
    1 => 2,
    2 => 'guitar',
  ),
  4 => 
  array (
    0 => 5,
    1 => 2,
    2 => 'banjo',
  ),
  5 => 
  array (
    0 => 6,
    1 => 3,
    2 => 'guitar',
  ),
  6 => 
  array (
    0 => 5,
    1 => 3,
    2 => 'banjo',
  ),
)

I need this to be sorted like so:

instruments
    acoustic
        banjo
        guitar
    electric
        banjo
        guitar

Thanks!

Answer 1

I don't know what exactly you want to do given that you have no code example, but isn't it easier to do like this:

SELECT * FROM my_table ORDER BY id, parent_id, title;

that orders first by id, then by parent_id when ids are the same and for title if ids and parent_ids are the same