While I was working on a project for our travel website, Carefreetrip.com Travel, I was faced with the need to develop a MySQL query using PHP that would allow me to pull a group of database objects that were in flat form and group them into an array by parent id’s and associated children, grandchildren, great grandchildren, etc., etc. (you get the idea) objects.

After going through the several options and lots of Googling, I discovered a way to pull the parent_id from the database, put the parent_id into one array and then put all of the children objects into a second array. Here is the code that I used -

$parent_refs = array();
$child_list = array();
$result = mysql_query(“SELECT item_id, parent_id, title FROM your_database ORDER BY item_name”);
while($data = @mysql_fetch_assoc($result)) {

$thisitem = &$parent_refs[ $data['id'] ];

$thisitem['parent_id'] = $data['parent_id'];
$thisitem['title'] = $data['title'];
$thisitem['id'] = $data['id'];

if ($data['parent_id'] == $thisitem) {
$child_list[ $data['id'] ] = &$thisitem;
} else {
$parent_refs[ $data['parent_id'] ][ $data['id'] ] = &$thisitem;
}

}

Basically this allows you to pull all of the items from “your_database”, assign the parents to one array ($parent_refs), while looping those items with corresponding parent_id’s and add them to a second array ($child_list) creating a multidimensional array.

You can view the results by using PHP’s print_r function or “print_r($child_list)” to view the multidimensional array.  I hope this helps someone!