state city tree list

[anujgarg]

Hi Everyone,

 

I have two tables - state and city.

 

Structure of table city -

 

      cid state_id cname

1 1         Delhi

2 1       Karnataka

3 2       TamilNadu

 

 

Structure of table state-

 

sid sname

1 India

2 Russia

3 Australia

4 Aruba

5 Netherlands

 

How can I apply recursive function to display the tree structure of state - city listing?

 

Please suggest.

 

TIA

 

Anuj

[Daniel0]

There is no need for recursion:

 

$db = new PDO('mysql:host=localhost;dbname=test', 'root', 'foo');

$res = $db->query('SELECT s.sname, c.cname FROM state AS s LEFT JOIN city AS c ON s.sid = c.state_id ORDER BY s.sname, c.cname');

$prevStateName = null;
foreach ($res->fetchAll() as $city) {
if ($city['sname'] !== $prevStateName) {
	echo $city['sname'] . PHP_EOL;
}
if ($city['cname'] !== null) {
	echo ' - ' . $city['cname'] . PHP_EOL;
}

$prevStateName = $city['sname'];
}

 

Output:

Aruba
Australia
India
- Delhi
- Karnataka
Netherlands
Russia
- TamilNadu

[kickstart]

Hi

 

That wouldn't really be used for a recursive function. Combine the 2 into one table (so that, say, areas would have cities as parents, cities would have states as parents, states would have countries as parents, etc) and then it would be a idea to recursively go through them.

 

A table such as:-

 

Id ParentId Name

1 0 India

2 0 Russia

3 0 Australia

4 0 Aruba

5 0 Netherlands

6 1 Delhi

7 1 Karnataka

8 2 TamilNadu

 

With you basic structure you would be best off using a normal SQL JOIN.

 

All the best

 

Keith

[Daniel0]

Id ParentId Name

1 0 India

2 0 Russia

3 0 Australia

4 0 Aruba

5 0 Netherlands

6 1 Delhi

7 1 Karnataka

8 2 TamilNadu

 

A NULL value would be better to represent the tree root because it specifically means that no such thing exists. When using InnoDB's referential integrity features, you would also get into trouble representing "no parent" with a numeric value (unless there is an object with ID 0 called "The Universe").

[kickstart]

Hi

 

Fair point, but it is just to explain the basic idea.

 

All the best

 

Keith