Changeset 8986 for src/db

Timestamp:

Mar 22, 2011, 12:05:48 PM (14 years ago)

Author:

dennisw

Message:

csv_to_db.php - scanned naar .csv bestanden, import naar db, verplaatst naar backup map

File:

• src/db/csv_to_db.php (modified) (2 diffs)

src/db/csv_to_db.php

@@ -2 +2 @@
 
 // edit with personal settings
-$location = '';
-$username = '';
-$password = '';
-$database = '';
-$table = '';
-$file = '.csv';
+$location = ' ';
+$username = ' ';
+$password = ' ';
+$database = ' ';
+
+$filelist = array();
+$filelist = scandir("csv");
+sort($filelist);
+unset($filelist[0]);
+unset($filelist[1]);
+unset($filelist[2]);
 
 
@@ -16 +21 @@
         or die("Error connecting to database: " . mysql_error());
 
-        
-// 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($file, "r")) !== FALSE) {
-        while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE) {
-                $lat=str_replace("N ", "", $data[0]);
-                $lon=str_replace("E ", "", $data[1]);
-                $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','$data[2]','$data[4]','$enc')");
-                if (!$query) {
-                        die('Invalid query: ' . 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]);
+                        $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','$data[2]','$data[4]','$enc')");
+                        if (!$query) {
+                                die('Invalid query: ' . mysql_error());
+                        }
                 }
+                fclose($handle);
+                rename("csv/".$file, "csv/backup/".$file);
         }
-        fclose($handle);
+
 }