Creating a recursive function in php to re-structure a recordset?

Question

Hello

I was hoping someone might be able to advise on how best to do this.

I have a database resultset that looks something like this:

PARENT       CHILD
Cat          Tiger
Cat          Lion
Cat          Panther
Mammal       Cat
Mammal       Dog
Creature     Mammal

What I require is a function that when passed this resultset, will return something that looks like this:

PARENT       CHILD
Cat          Tiger
Cat          Lion
Cat          Panther
Mammal       Tiger
Mammal       Lion
Mammal       Panther
Creature     Tiger
Creature     Lion
Creature     Panther

To describe the process of such a function:

• if a value in the CHILD field does not exist in the PARENT field, we simple add the row to $newArray.

• if the value in the CHILD field does exist in the PARENT field, we want to examine any instances where this value occurs in the PARENT field and apply the same consideration, only adding to $newArray where the CHILD does not occur in the PARENT field, and associating the ultimate CHILD with the original PARENT.

I recognise this involves recursion while iterating over the parent field. I've attempted this but have ended up getting stuck...

Thanks for any help!

(Note that if is better served doing this using an SQL stored procedure, I am open to suggestions. I am using mySQL, and understood that recursion was not supported, hence me attempting this in php).

Answer 1

Made a shot trying to translate your pseudo code to php:

$map = array( "Cat" => array("Tiger","Lion",....

getChildren($map,'Mammal') ===> (Tiger,Lion,Panther,Dog)

function getChildren($map,$parent)
{
  if (!isset($map[$parent]))//if a value in the CHILD field does not exist in the PARENT field
    return array($parent); 
  $return = array();      
  $children=$map[$parent];

  foreach($children as $child)//we want to examine any instances where this value occurs in the PARENT field 
  {
    $return = merge($return, getChildren($child));//and apply the same consideration
  }
  return $return;
}

Not sure I didn't make any mistakes. However this will also give:

Creature  Dog
Mammal    Dog

Or why should these be excluded?