Mysql – Creating a table with parent_id from a flat table

MySQL

I have a flat table

(id, city, county, state),

Example 

(1,'Beckingen', 'Merzig-Wadern', 'Saarland')

A city belongs to a county. A county belongs to a state.

First my attempt was to divide the data into 3 tables cities, counties, and states and to build up the associations via linking tables (cities_counties). Now I want to create a 'locations' table out of it, where county is parent of city and state is parent of county.

locations: 
(id, name, parent_id, type)

 (type is 'city','county', or 'state')

What would be best practice to create the adjacent list from the flat list?

I'm working in a LAMP environment, just in case a php script would fit.

Best Answer

I wrote a php Script that does the job :)

not generic at all and straightforward, but it works

mysql_query('truncate table list');

$cities = array();

$res = mysql_query('select city from temps');

while ($row = mysql_fetch_assoc($res))
    $cities[] = $row['city'];

$counties = array();

$res = mysql_query('select distinct county from temps');

while ($row = mysql_fetch_assoc($res))
    $counties[] = $row['county'];

$states = array();

$res = mysql_query('select distinct state from temps');

while ($row = mysql_fetch_assoc($res))
    $states[] = $row['state'];

//insert germany as root node

mysql_query('insert into list(name,parent_id,type) values("Germany", NULL, "Country")');
$rootid = mysql_insert_id();


foreach ($states as $state) {
    mysql_query("insert into list(name,parent_id, type) values('$state','$rootid','State')");
}

foreach ($counties as $county) {
    //determine state
    $res = mysql_query("select state from temps where county='$county' LIMIT 1");
    $row = mysql_fetch_row($res);
    $state = $row[0];

    $res = mysql_query("select id from list where name='$state' LIMIT 1");
    $row = mysql_fetch_row($res);
    $parent_id = $row[0];

    mysql_query("insert into list(name,parent_id,type) VALUES('$county','$parent_id','County')");
}


foreach ($cities as $city) {
    //determine state
    $res = mysql_query("select county from temps where city='$city' LIMIT 1");
    $row = mysql_fetch_row($res);
    $county = $row[0];

    $res = mysql_query("select id from list where name='$county' LIMIT 1");
    $row = mysql_fetch_row($res);
    $parent_id = $row[0];

    mysql_query("insert into list(name,parent_id,type) VALUES('$city','$parent_id','City')");
}
Related Question