从csv到mysql数据库php的插入不完整
从csv到mysql数据库php的插入不完整
提问于 2016-01-21 18:54:55
我在将CSV文件中的数据记录插入mysql数据库时遇到错误。我有一个运行良好的脚本。它将CSV文件中的数据插入到mysql数据库。它有大约40,000条记录。但是在运行脚本之后，它只插入了大约1000条记录，并且没有错误地停止。可能的问题是什么？请帮帮忙。
下面是我的插入数据的脚本。
include('dbconnection.php');
if (($handle = fopen("hotels.csv", "r")) !== FALSE) {
$flag = true;
$id=1;
while (($data = fgetcsv($handle, 100000000, ",")) !== FALSE) {
if($flag){
$flag = false;
continue;
}
$hotelid = mysql_real_escape_string($data[0]); //var_dump($hotelid); exit();
$hotelname = mysql_real_escape_string($data[1]);
$address1 = mysql_real_escape_string($data[2]);
$address2 = mysql_real_escape_string($data[3]);
$country = mysql_real_escape_string($data[4]);
$city = mysql_real_escape_string($data[5]);
$postcode = mysql_real_escape_string($data[6]);
$telephone = mysql_real_escape_string($data[7]);
$hotelfax = mysql_real_escape_string($data[8]);
$hotelemail = mysql_real_escape_string($data[9]);
$longitude = mysql_real_escape_string($data[10]);
$latitude = mysql_real_escape_string($data[11]);
$website = mysql_real_escape_string($data[12]);
$location = mysql_real_escape_string($data[13]);
$description = mysql_real_escape_string($data[14]);
$starrating = mysql_real_escape_string($data[15]);
$statecode = mysql_real_escape_string($data[16]);
$select_query = "SELECT * FROM `hotelDB`.`hotels` WHERE `hotelid` = $hotelid";
if(mysql_query($select_query) != '$hotelid'){
$sql = "INSERT IGNORE INTO `hotelDB`.`hotels` (`id`,`hotelid`, `hotelname`, `address1`,`address2`,`country`,`city`,`postcode`,`telephone`,`hotelfax`,`hotelemail`,`longitude`,`latitude`,`website`,`location`,`description`,`starrating`,`statecode`) VALUES ('$id','$hotelid', '$hotelname', '$address1','$address2', '$country', '$city', '$postcode', '$telephone', '$hotelfax', '$hotelemail', '$longitude', '$latitude', '$website', '$location', '$description', '$starrating', '$statecode')";
echo $sql;
$retval = mysql_query($sql,$conn);
if(! $retval )
{
die('Could not enter data: ' . mysql_error());
}
echo "<p style='color: green;'>Entered data having id = " .$id. " successfully</p><br>";
$id++;
}
}
echo "<br><p style='color: orange;'>Congratulation all data successfully inserted</p>";
fclose($handle);
}
mysql_close($conn);`插入某些值后停止插入。这是代码停止执行的示例屏幕截图。
回答 2
Stack Overflow用户
发布于 2016-01-21 19:17:42
您可以使用多行插入来尝试一个查询。在下面的示例中，我使用一个insert查询插入500行。您可以将其更改为1000。
<?php
include('dbconnection.php');
if (($handle = fopen("grnconnect-dump-hotels.csv", "r")) !== FALSE) {
//var_dump( $handle);
$flag = true;
$id=1;
$counter=0;
$sql = "INSERT IGNORE INTO `hotelDB`.`hotels` (`id`,`hotelid`, `hotelname`, `address1`,`address2`,`country`,`city`,`postcode`,`telephone`,`hotelfax`,`hotelemail`,`longitude`,`latitude`,`website`,`location`,`description`,`starrating`,`statecode`) VALUES";
$saveString="";
while (($data = fgetcsv($handle, 100000000, ",")) !== FALSE) {
//var_dump($data); exit();
if($flag){
$flag = false;
continue;
}
$hotelid = mysql_real_escape_string($data[0]); //var_dump($hotelid); exit();
$hotelname = mysql_real_escape_string($data[1]);
$address1 = mysql_real_escape_string($data[2]);
$address2 = mysql_real_escape_string($data[3]);
$country = mysql_real_escape_string($data[4]);
$city = mysql_real_escape_string($data[5]);
$postcode = mysql_real_escape_string($data[6]);
$telephone = mysql_real_escape_string($data[7]);
$hotelfax = mysql_real_escape_string($data[8]);
$hotelemail = mysql_real_escape_string($data[9]);
$longitude = mysql_real_escape_string($data[10]);
$latitude = mysql_real_escape_string($data[11]);
$website = mysql_real_escape_string($data[12]);
$location = mysql_real_escape_string($data[13]);
$description = mysql_real_escape_string($data[14]);
$starrating = mysql_real_escape_string($data[15]);
$statecode = mysql_real_escape_string($data[16]);
if(trim($counter)=="500") {
$sql = $sql.''.rtrim($saveString,",");
echo $sql;echo "<br /";
mysql_query($sql);
$counter=0;
$saveString="";
}
$saveString .="('".$id."','".$hotelid."', '".$hotelname."', '".$address1."','".$address2."', '".$country."', '".$city."', '".$postcode."', '".$telephone."', '".$hotelfax."', '".$hotelemail."', '".$longitude."', '".$latitude."', '".$website."', '".$location."', '".$description."', '".$starrating."', '".$statecode."'),";
$counter++;
}
if(trim($saveString)!=='') {
$sql = $sql.''.rtrim($saveString,",");
echo $sql;echo "<br /";
mysql_query($sql);
}
echo "<br><p style='color: orange;'>Congratulation all data successfully inserted</p>";
fclose($handle);
}
mysql_close($conn);
?>Stack Overflow用户
发布于 2016-01-21 19:57:51
通过脚本插入有什么特殊的原因吗？否则，我建议你直接手动插入，或者让数据库来做，因为这样做效率更高。MySQL提供了这样的函数：http://www.mysqltutorial.org/import-csv-file-mysql-table/
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34921794
复制相关文章
相似问题
腾讯云开发者
