Parent Child Relationships PHP/MYSQL

Question

I have a table like this:

• id
• name
• parent_id

I then want to select certain rows based on their id, so something like this:

SELECT * 
  FROM TABLE 
 WHERE id IN ('1', '5', '8', '9', '35')  

I want to, from this query, also show the parent/child relationship, like:

id   parent  
-----------
1    0  
5    1  
8    0  
9    8  
35   9  

So the final output would look something like this:

1  
--5  

8   
--9  
 ----35  

Do I do this outside of mysql, i have tried using arrays, but can't figure it out, or
Do I do it inside MYSQL, which i don't know how to do that either.

Answer 1

Here is what I was able to come with which seems to be working great.

PS-Sorry about the formatting, can't figure it out :( (fixed?)

1. I grab my parent_id and id from MYSQL and put it into an arraly where the array keys are the id's and the values are the parents, so with in the while loop for mysql, something like this: $testarray[$id] = $parent_id;

2. Then I run it through the functions below, and it orders it just how I need it.

   function retrieveSubTree($parent, $myarray) {
       $tempArray = $myarray;
       $array = array();           
       //now we have our top level parent, lets put its children into an array, yea!
       while ($child = array_search($parent, $tempArray)) {
           unset($tempArray[$child]);
           //now lets get all this guys children
           if (in_array($child, $tempArray)) {
               $array[$child] = retrieveSubTree($child, $tempArray);
           } else {
               $array[$child] = true;
           }
       }//end while
       return (!empty($array)) ? $array : false;
   }
   
   function retrieveTree($myarray) {
       $array = array();
       $counter = 0;
       foreach ($myarray as $key => $value) {
           $child = $key;
           $parent = $value;
           //if this child is a parent of somebody else
           if (in_array($child, $myarray) && $parent != '0') {
               while ($myarray[$parent] != '' && $myarray[$parent] != '0') {
                   $newparent = $myarray[$parent];
                   $parent = $newparent;
               }
               if (!array_key_exists($parent, $array)) {
                   $array[$parent] = retrieveSubTree($parent, $myarray);
               }
           } else {
               //now make sure they don't appear as some child
               if (!array_key_exists($parent, $myarray)) {
                   //see if it is a parent of anybody
                   if (in_array($child, $myarray)) {
                       $array[$child] = retrieveSubTree($child, $myarray);
                   } else {
                       $array[$child] = true;
                   }
               }//end if array key
           }//end initial in array
       }//end foreach
       return (!empty($array) ? $array : false);
   } 
   
   $test = array(
       '1'=>'15',
       '2'=>'1',
       '3'=>'1',
       '4'=>'0',
       '5'=>'0',
       '6'=>'4',
       '7'=>'6',
       '8'=>'7',
       '9'=>'2',
       '10'=>'9'
   );
   
   print_r(retrieveTree($test)); 
   

Answer 2

Without changing your table structure, this requires recursion, which MySQL does not support. You'll have to do it elsewhere. You can write a recursive function in PHP to use, for example, breadth-first search to build your array. Here it looks like you are using parent_id of 0 to denote a top-level object. You can search over your results, and add to your array every object whose parent is zero, which will give you an array with 1 and 8. Then you can recurse: find all the results with a parent of 1, and add that as a subarray to 1; then find all the results with a parent of 8 and add those as a subarray of 8. Continue doing this for each level until you've run out of results.

As other posters pointed out, you can do this natively in MySQL if you can change the table structure.