php - 从csv到mysql数据库php的不完整插入

Question

我在将数据记录从 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);`

插入一些值后停止插入。这是代码停止执行的示例屏幕截图。

Answer 1

您可以尝试使用多行 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);
?>