Example data for importing streets

hanoi-streets.csv

wp-example-sql-3.php

<?php
add_action('init', 'npn_location_database_init');
function npn_location_database_init() {
  global $wpdb;
	$charset_collate = $wpdb->get_charset_collate();

  require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
  
  $streets_table_name = $wpdb->prefix . 'npn_streets';

    $streets_table_exist = $wpdb->query("SHOW TABLES LIKE '$streets_table_name'");

    if ( empty($streets_table_exist) ) {
        $create_streets_table_sql = "CREATE TABLE IF NOT EXISTS $streets_table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name tinytext NOT NULL,
            slug text NOT NULL,
            code text NOT NULL,
            wardCode text NOT NULL,
            wardId text NOT NULL,
            wardSlug text NOT NULL,
            wardName text NOT NULL,
            districtCode text NOT NULL,
            districtId text NOT NULL,
            districtSlug text NOT NULL,
            districtName text NOT NULL,
            cityCode text NOT NULL,
            cityId text NOT NULL,
            citySlug text NOT NULL,
            cityName text NOT NULL,
            PRIMARY KEY (id)
        ) $charset_collate;";
        dbDelta( $create_streets_table_sql );
    }
}

add_action('wp', 'run_sql');
function run_sql() {
    global $wpdb;

    $table_name = $wpdb->prefix .'npn_streets';

    $file = 'hanoi-streets.csv';
    $file_content = file_get_contents( get_template_directory() . '/functions/locations/' . $file );
    $lines = explode(PHP_EOL, $file_content);

    $created = 0;
    $failure = 0;
    $existing = 0;

    foreach ($lines as $line) {
        $row_data = str_getcsv($line);

        $existing_record = $wpdb->get_row($wpdb->prepare("SELECT name FROM `$table_name` WHERE `name` = '%s'", $row_data[3]));

        if ( !empty($existing_record) ) {
            $existing++;
            echo 'Existing record ' . $existing_record->name . '<br>';
        } else {
            $sql = $wpdb->insert($table_name, array(
                'name' => sanitize_text_field($row_data[3]),
                'slug' => sanitize_title($row_data[3]),
                'wardName' => !empty($row_data[2]) ? sanitize_text_field($row_data[2]) : '',
                'wardSlug' => !empty($row_data[2]) ? sanitize_title($row_data[2]) : '',
                'districtName' => !empty($row_data[1]) ? sanitize_text_field($row_data[1]) : '',
                'districtSlug' => !empty($row_data[1]) ? sanitize_title($row_data[1]) : '',
                'cityName' => !empty($row_data[0]) ? sanitize_text_field($row_data[0]) : '',
                'cityCode' => !empty($row_data[0]) ? sanitize_title($row_data[0]) : '',
            ));
    
            if ( !empty($sql) ) {
                $created++;
                echo 'Success create ' . $row_data[3] . '<br>';
            } else {
                $failure++;
                echo 'Failed to create ' . $row_data[3] . '<br>';
            }
        }
    }

    echo 'RESULT: Success: ' . $created . ' - Failure: ' . $failure . ' - Existing: ' . $existing;

    exit;
}