cities_csv_to_sql.php

<?php
/*
 * Field from dataset (CSV file)
 * 0 geoname_id
 * 1 locale_code
 * 2 continent_code
 * 3 continent_name
 * 4 country_iso_code
 * 5 country_name
 * 6 subdivision_1_iso_code
 * 7 subdivision_1_name
 * 8 subdivision_2_iso_code
 * 9 subdivision_2_name
 * 10 city_name
 * 11 metro_code
 * 12 time_zone
 * */

$stmtInsertContinents = $dbh->prepare("INSERT INTO Continents VALUES (:continent_code, :continent_name)");
$stmtGetContinents = $dbh->prepare("SELECT continent_code FROM Continents WHERE continent_code = :continent_code");
if (($handle = fopen("cities.csv", "r")) !== FALSE) {
    // First get the column headers
    $columnNames = fgetcsv($handle, 1000, ",");

    // loop over the rest of the rows in the csv
    while (($data = fgetcsv($handle, 1000, ",")) !== false) {
        // combine the column names with the row values
        $row = array_combine($columnNames, $data);

        // Does the continent already exist in the database?
        $params = ["continent_code" => $row['continent_code']];
        if ($stmtGetContinents->execute($params)) {
            if (!$stmtGetContinents->fetchAll(PDO::FETCH_ASSOC)) {
                // add it
                $stmtInsertContinents->execute(["continent_code" => $row["continent_code"], "continent_name" => $row["continent_name"]]);
            }
        }
        
        // Do the same thing for countries/states/cities table here
    }
}