wierd错误从一个csv文件读取和导入数据到mysql使用php [英] wierd bug when reading from a csv file and importing data into mysql using php

问题描述

好，所以我有一个php脚本插入数据到mysql表，它看起来像这样：

Okay so I have a php script to insert data into a mysql table it looks like this:

<?php
$connect = mysql_connect('localhost','my_username','my_pass');
if (!$connect) {die('Could not connect to MySQL: ' . mysql_error());}
$cid =mysql_select_db('geolocation',$connect);
// supply your database name
define('CSV_PATH','/var/www/geolocation/');
// path where your CSV file is located
$csv_file = CSV_PATH . "new_Location.csv"; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
$k = 0;
while (!feof($csvfile)) {
   $csv_data[] = fgets($csvfile, 1024);
   $csv_array = explode(",", $csv_data[$i]);
   $insert_csv = array();
   if(isset($csv_array[0]) && isset($csv_array[1]) &&  isset($csv_array[2]) &&  isset($csv_array[3]) &&  isset($csv_array[4]) &&  isset($csv_array[5]) &&  isset($csv_array[6]) &&  isset($csv_array[7]) &&  isset($csv_array[8])){
     $insert_csv['ID'] = $csv_array[0];
     $insert_csv['country'] = $csv_array[1];
     $insert_csv['region'] = $csv_array[2];
     $insert_csv['city'] = $csv_array[3];
     $insert_csv['postalCode'] = $csv_array[4];
     $insert_csv['latitude'] = $csv_array[5];
     $insert_csv['longitude'] = $csv_array[6];
     $insert_csv['other'] = $csv_array[7];
     $insert_csv['another'] = $csv_array[8];
     $query = "INSERT INTO City (locId, country, region, city, postalCode, latitude, longitude) VALUES('".$insert_csv['ID']."','".$insert_csv['country']."','".$insert_csv['region']."','".          $insert_csv['city']."','".$insert_csv['postalCode']."','".$insert_csv['latitude'].",,'".$in sert_csv['longitude']."'')";
  $n=mysql_query($query, $connect );
   $i++;
   }
   if($k==1000){
    echo $i . " \n";
    $k = 0;
   }
   $k++;
}
fclose($csvfile);
echo "File data successfully imported to database!!";
mysql_close($connect);
?>

现在当我运行脚本一切似乎工作正常，我得到 echo $ i 每次一次，最终脚本结束没有任何错误或任何东西，但是，当我看看我的mysql（通过phpmyadmin）我没有看到任何行添加到表...我也使用了过去的另一个修改版本的脚本，它工作完美（除了慢），但我仍然很绿色，当谈到mysql，似乎不知道发生了什么...这里是我的表格格式

now when I run the script everything seems to work properly and I get echo $i every once in a while and eventually the script ends with no errors or anything however when I look into my mysql (through phpmyadmin) I do not see any of the rows added to the table... I have also used another modified version of this script in the past and it worked perfectly (other than slow), However I'm still pretty green when it comes to mysql and can't seem to figure out what's going on... here is my table format

 locId --> int(11)
 country --> varchar(2)
 region --> varchar(2)
 city --> varchar(50)
 postalCode --> varchar(8)
 latitude --> varchar(10)
 longitude --> varchar(10)

以及csv文件中的示例：

and also a sample from the csv file:

 46,CK,NA,NA,NA,-21.2333,-159.7667,NA,NA

任何帮助为什么这不是工作，甚至我可以去调试它将非常感谢！

any help with why this isn't workign or even how I can go about debugging it would be very much appreciated!

还要注意（我不希望在我的表中的csv中的最后一个值），所以这些都是从目的插入语句中省略的

also note (I do not wish to have the last to value in the csv in my table) so those are ommited from the insert statement on purpose

推荐答案

我这样做：

$file = fopen("$link_file","r")or die("file dont exist");
while (!feof($file )){
$campo = fgetcsv($file,4096,",");

$loadsql = "INSERT INTO temporal_table(id,state,etc) VALUES ('$campo[0]','$campo[1]','etc');";
mysql_query($loadsql) or die(mysql_error());

}
fclose($file );

我看不到你使用 fgetcsv 函数。
和，你尝试回显数据在那段时间？
使用类似这样的东西：

i dont see you using fgetcsv function. and, did you try to echo the data in the while? use something like this:

echo $i." line query ".$query." <br>";

EDIT

p>

try this:

$csv_file = CSV_PATH . "new_Location.csv"; 
$file = fopen("$csv_file","r")or die("file error");
while (!feof($file)){

$insert_csv = fgetcsv($file,4096,",");
$query = "INSERT INTO City (
               locId, 
               country, 
               region, 
               city, 
               postalCode, 
               latitude, 
               longitude                //removed all the simple quotes..
           ) VALUES (
               '$insert_csv[0]',
               '$insert_csv[1]',
               '$insert_csv[2]',
               '$insert_csv[3]',
               '$insert_csv[4]',
               '$insert_csv[5]',
               '$insert_csv[6]'
           )";
    mysql_query($query) or die(mysql_error());
    }
    fclose($csvfile);

这篇关于wierd错误从一个csv文件读取和导入数据到mysql使用php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！