将csv导入mysql数据库 - 错误


Import csv into mysql database - error

我创建了一个php脚本,我将放入cron作业中,应该在午夜执行,但这不是问题。问题是当 cron 运行我的代码时,它会从数据库中删除所有记录,但不从 CSV 文件中插入新记录。有什么提示吗?

PHP代码如下:

<?php 
# I run this script from a cron job every night to update 
# the mysql database I use with my employee web site 
# so it matches my local database every day.  Feel free to 
# modify it to meet your specific needs.  If you find it 
# usefull, drop me an email and let me know. 
# edit the follow six items to use the script 
# first connect to your mysql database 
# i have my connection settings in a diferent file 
# so i just include that file in all my scripts 
include("db.php"); 
# assign the tables that you want to import to to the table array 
$table = array( 
                'table1', 
                'table2', 
                'table3', 
                'table4', 
                'table5', 
        ); 
# if the first row of your csv file contains column headings: 
# $columnheadings=1 
# if the first row does not contain column headings and should be imported: 
# $columnheadings=0 
$columnheadings = 0; 
# contains the email address you want the results sent to 
$emailaddress = "user@domain.com"; 
# contains the subject you want the message to have 
$subject = "Enter Subject Here"; 
# contains the email address that will show in the from line 
$emailfrom = "user@domain.com"; 
# perform the required operations for every table listed in the table array 
foreach ($table as $tablename) { 
    # empty the table of its current records 
    $deleterecords = "TRUNCATE TABLE `$tablename`"; 
    mysql_query($deleterecords); 
    # intialize your counters for successful and failed record imports 
    $pass = 0; 
    $fail = 0; 
    # the csv file needs to be the same name as the table, 
    # comma seperated with the columns in the same order as the table, 
    # and in the same dir as this script 
    $filecontents = file ("$tablename.csv"); # .csv is added to the table name to get the name of the csv file 
    # every record in the csv file will be inserted into the table unless an error occurs with that record 
    for($i=$columnheadings; $i<sizeof($filecontents); $i++) { 
        $insertrecord = "Insert Into `$tablename` Values ($filecontents[$i])"; 
        mysql_query($insertrecord); 
        if(mysql_error()) { 
            $fail += 1;     # increments if there was an error importing the record 
        } 
        else 
        { 
            $pass += 1;        # increments if the record was successfully imported 
        } 
    } 
    # adds a line to the email message we will send stating how many records were imported 
    # and how many records failed for each table 
    $message .= "Table $tablename: Success=$pass  Failure=$fail 'n"; 
} 
# set to the date and time the script was run 
$runtime = (date("d M Y H:i")); 
# add the run time to the body of the email message 
$message .= "'nTime of the message: $runtime (server time zone)'n'n"; 
# Send the email message 
mail($emailaddress, $subject, $message, "From: '$emailfrom'"); 
?>

您的代码不会检查文件是否存在 - 那么,如果它不在您想要的地方怎么办?根据您从 cron 运行它的方式,它可能从与从 Web 运行它不同的目录运行。

表的模式是什么,最后你在$message得到什么? 您是否将所有行都显示为失败?

添加:

由于所有行都失败,这意味着插入语句中存在一些问题,或者 csv 和目标表之间的架构不匹配。 试试这个

    # and in the same dir as this script 
    $filecontents = file ("tablename.csv"); # .csv is added to the table name to get the name of the csv file 
    # every record in the csv file will be inserted into the table unless an error occurs with that record 
    for($i=$columnheadings; $i<sizeof($filecontents); $i++) { 
        $insertrecord = "Insert Into `$tablename` Values ($filecontents[$i])"; 
        mysql_query($insertrecord); 
        if(mysql_error()) { 
            $fail += 1;     # increments if there was an error importing the record
             //this is include exact error in email, and will help you to identify the
             //problem very easily.
             $message .= mysql_errno() . ": " . mysql_error() . "'n"; 
        } 
        else 
        { 
            $pass += 1;        # increments if the record was successfully imported 
        } 
    }

试试这个,让我知道你的发现?