1

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.

0
1

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')");
}
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.