Joining hierarchical page data

Hello,

I am having some trouble wrapping my head around the best way to approach my current issue. I mysql table storing page data structure as shown below:

CREATE TABLE `Pages` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `keywords` varchar(255) NOT NULL DEFAULT '',
  `permalink` varchar(155) NOT NULL COMMENT 'URI associated with the page',
  `content` mediumtext NOT NULL,
  `order` int(11) NOT NULL DEFAULT '0',
  `parent` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Code (markup):

I was hoping to create a query that would pull the data from this table in the hierarchical format desired without having to do any PHP processing afterwards. Unfortunately, I'm not quite sure how I would go about it. Essentially, I want the output to be similar to the structure below:

$row = array(
    'title' = 'page title',
    ....
    'parent' = array(
        'title' = 'parent title',
        ....
        'parent' = array(....)
    );
);

Code (markup):

Does anyone know how I would go about doing this? I will be using this information to display the pages in a navigation menu primarily, so if anyone has any alternative suggestions for pulling the information then those are welcome as well.


Thanks in advance!
~Thorlax

 

Consider joining the table to itself to get the parent for every id. It depends on how deep the parent hierarchical is; if it is one level deep you have to join once, two level you have to join twice, etc.