source: src/db/csv_to_db.php@ 9640

<?php

// edit with personal settings
$location = 'localhost:3306';
$username = 'root';
$password = 'dsql';
$database = 'project_heatmap';

$filelist = array();
$filelist = scandir("csv");
sort($filelist);
unset($filelist[0]);
unset($filelist[1]);
unset($filelist[2]);


// connect to database
mysql_connect($location, $username, $password)
        or die("Error connecting to mysql: " . mysql_error());
mysql_select_db($database)
        or die("Error connecting to database: " . mysql_error());



foreach($filelist as $file) {

$table = substr($file, 0, -4);

        // create table
        mysql_query("
                CREATE TABLE IF NOT EXISTS ".$table." (
                        latitude double(9,7) NOT NULL,
                        longitude double(9,7) NOT NULL,
                        ssid varchar(45) NOT NULL,
                        mac varchar(45) NOT NULL,
                        encryption tinyint(4) NOT NULL,
                        ID int(11) NOT NULL AUTO_INCREMENT,
                        PRIMARY KEY (ID)
                )
        ");


        // .csv file to open
        if (($handle = fopen("csv/".$file, "r")) !== FALSE) {
                while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE) {
                        $lat=str_replace("N ", "", $data[0]);
                        $lon=str_replace("E ", "", $data[1]);
                        $ssid=str_replace("'", "", $data[2]);
                        $enc = substr($data[8], -2, 1);
                        echo "\"".$lat.", ".$lon."\", \"".$data[2]."\", \"".$data[4]."\", \"".$enc."\"<br />\n";
                        $query=mysql_query("INSERT INTO ".$table." (latitude, longitude, ssid, mac, encryption) VALUES ('$lat','$lon','$ssid','$data[4]','$enc')");
                        if (!$query) {
                                die('Invalid query: ' . mysql_error());
                        }
                }
                fclose($handle);
                rename("csv/".$file, "csv/backup/".$file);
        }

}

?>