All kids of Asia

PHP
1 
mom         kid

earth       Asia
earth       Europe
Europe      France
Asia        Korea
earth       Africa
Asia        China
China       Peking
Europe      Germany
Germany     Berlin
Korea       Seoul
Seoul       Kangnam
Kangnam     Hak-dong
Hak-dong    Hak station
Asia        Japan
Hak station 1st floor
Kangnam     Soecho-dong

I have myTable like the above.

I have the code below.

$kid='Asia';
$list=mysql_query("SELECT mom, kid FROM myTable0 WHERE mom='$kid' ORDER BY kid desc");

while($rows=mysql_fetch_array($list)){
$mom=$rows['mom'];  $kid=$rows['kid'];
echo $mom.' '.$kid.'<br>';}

The result of the code above is the following.

Asia Korea 
Asia Japan 
Asia China

I have another code below.

$kid='Asia';
$list=mysql_query("SELECT mom, kid FROM myTable0 WHERE mom='$kid' ORDER BY kid desc");

while($rows=mysql_fetch_array($list)){
$mom=$rows['mom'];  $kid=$rows['kid'];
$list=mysql_query("SELECT mom, kid FROM myTable0 WHERE mom='$kid'");
echo $mom.' '.$kid.'<br>';}

The result of the code above is like the following.

Asia         Korea 
Korea        Seoul 
Seoul        Kangnam 
Kangnam      Hak-dong 
Hak-dong     Hak station
Hak station  1st floor

I want my target result below.

Asia         Korea 
Korea        Seoul 
Seoul        Kangnam 
Kangnam      Hak-dong 
Hak-dong     Hak station
Hak station  1st floor
Kangnam      Seocho-dong
Asia         Japan
Asia         China
China        Peking

Can I get my target result above with your help?

3

Just create a recursive function which would allow you to start all the way up at Earth if you want to…

function getTableKids($mom) {
      $list=mysql_query("SELECT mom, kid FROM myTable0 WHERE mom='$mom' ORDER BY kid desc");
      while($rows=mysql_fetch_array($list)){
          echo $mom.' '.$rows['kid'].'<br>';
          getTableKids($rows['kid']);
       }
}

getTableKids('Asia'); // call the function
?>
4

#1: Don’t use the mysql_ functions. Replace them with mysqli_ at the very least.

#2: The other way of doing it involves changing your table structure to a tree form. Dave’s recursive function will get you your result if you dont mind multiple queries being sent to the server.

1 Like
5

It looks like the sort of thing where you would have a table of place names, then a table which defines the relationship of the places, that would be like the table you have with mom and kid fields, only the values would be IDs from the place table.

Prepared statements (in PDO and mysqli, not in mysql) will kill two birds with one stone.
You can send the query once with placeholders in place of the variables, then call it multiple times with different values. It will be more efficient than multiple queries and also plug the security hole you have.

6

Actually it was more leaning towards Hierarchical Data

1 Like
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.