I was a bit of the way through a possible answer and realised I'd forgotten something important 🙁.
Any how; what you'd probably end up doing is having the query inside a loop, which starts at 0 and gives you the page info for the desired root, then goes on to find the root's children, then the root's children's children, etc. So at each step of the loop, you look for all the pages whose parents are those that you retrieved in the previous step. Keep doing this until you run out of descendants.
These are all going into one steadily-growing array, which effectively ends up mirroring the relevant rows in the database. Using mysql_fetch_array() or mysql_fetch_assoc() get an array for each row in turn you (a) store that array as a new element in the mirror array, indexed by page ID (so that, e.g., page 651 is stored in $tree_array[651]); and (b) note it's page_id, because in the next iteration of the loop you'll be looking for pages WHERE the parent_id is IN ( ... and here you'll provide a list of all the ID's just obtained ...)
And the loop repeats, as I say, until no more pages are being pulled out (is there a risk of loops? In that case, weed out IDs for pages already retrieved out of that list in step (b)).
So now you've got all the rows in the tree, and only the rows in the tree, and they're all in a single array. Now, we can look at a page and see who its parent is, but what we'll really be wanting is to look at a page and see who its children are. Okay, so each page in the array will need a new field, called 'children' I guess, which will be an array of all of that page's children's IDs. That's a quick loop:
foreach($tree_array as $k=>$v) $tree_array[$k]['children']=array();
Now with the children arrays created, we just go through the loop again. We look at each page's parent_ID, and append the page's ID to its parent's children array. To make this especially easy, remember that each page is stored in the $tree_array under its ID, so we don't have to go searching for it.
foreach($tree_array as $k=>$v)
{ if(isset($tree_array[$v['parent_id']]))
$tree_array[$v['parent_id']]['children'][]=$k;
}
Where the isset() is because the parent of the root isn't in the array.
So here's a quick summary of the bit after the array is built. In the example, page 1 is the root (and its parent is 98), its children are pages 12 and 33; page 12 has pages 4 and 42 as its children, and so on...
<?php
$tree_array = array(
651=>array('id'=>651, 'parent'=>98, 'other stuff...'),
12=>array('id'=>12, 'parent'=>651, 'other stuff...'),
33=>array('id'=>33, 'parent'=>651, 'other stuff...'),
4=>array('id'=>4, 'parent'=>12, 'other stuff...'),
42=>array('id'=>42, 'parent'=>12, 'other stuff...'),
61=>array('id'=>61, 'parent'=>33, 'other stuff...'),
102=>array('id'=>102, 'parent'=>61, 'other stuff...'),
8=>array('id'=>8, 'parent'=>42, 'other stuff...'),
9=>array('id'=>9, 'parent'=>4, 'other stuff...'));
foreach($array as $k=>$v) $tree_array[$k]['children']=array();
foreach($tree_array as $k=>$v)
{ if(isset($tree_array[$v['parent']]))
$tree_array[$v['parent']]['children'][]=$k;
}
print_r($tree_array);
?>
