Finding all inner records in recursive

[joon1]

I have a table like the below code mom kid
(1) earth Asia
(2) earth Europe
(3) Asia Korea
(4) Asia Russia
(5) Europe Russia
(6) Earth Africa
(7) Africa Egypt
(8) Europe Germany
(9) Germany Berlin
(10) Asia China
(11) Korea Seoul
(12) Europe France
(13) Seoul Kangnam
(14) Asia Japan
(15) China Peking
(16) Japan Tokyo
(17) Korea Pusan[/code]

I like to find all records which are inner of $kid1.

if $kid1 is “Asia”, I like to echo like the below result.

Korea Seoul Kangnam Pusan Russia China Peking Japan Tokyo

if $kid1 is “Korea”, The result will be like the.

Seoul Kangnam Pusan

if $kid1 is “Europe”, the below result.is what I want.

Russia Germany Berlin France

For getting the result above, I write the code below in php.

[code]$kid1='“Asia”;

$list1=mysql_query(“select kid from tree where mom=‘$kid1’ order by id”);

while($rows=mysql_fetch_array($list1))
{ $kid=$rows[‘kid’];
if($kid!==‘’)
{ echo $kid.‘
’;
$list2=mysql_query(“select kid from tree where mom=‘$kid’ order by id”);

  while($rows=mysql_fetch_array($list2))
  {  $kid=$rows['kid']; 
     if($kid!=='')
     {  echo '&nbsp; '.$kid.'<br>';
        $list3=mysql_query("select kid from tree where mom='$kid' order by id");

        while($rows=mysql_fetch_array($list3))
        {  $kid=$rows['kid'];
           if($kid!=='')
           { echo '&nbsp; &nbsp; '.$kid.'<br>';
           }
        }  
     }  
  }  

}
}
[/code]The code above in PHP works fine, but I like to find another code due to the problem below.

I have to make the query inside “while clause” in php continuously because I don’t know how many recursive kids are remained.

Do you have any other solutions, especially with DB query ?

[r937]

here is a solution, but again, you have to know how many levels you want to return –

Categories and Subcategories