php - 使用php将csv文件插入mySql

Question

我正在使用下面的代码将我的 csv 文件插入到我的数据库中，当我运行它时，我得到 0 行受影响但插入了 1 行。但是在我的 csv 文件中，我有 30 条记录，我不确定我哪里出错了。我在 WAMP 服务器上使用 phpMyAdmin。

这是我的表的创建：

$query_Customer = "Create table tbl_Customer
(
    customer_id int Primary Key auto_increment,
    customer_name varchar(50) NOT NULL,
    customer_email varchar(50) NOT NULL,
    customer_number char(10) NOT NULL,
    customer_address varchar(50) NOT NULL);";

这是我插入 csv 文件的代码：

$sql = "LOAD DATA LOCAL INFILE 'txt/customerData.csv'
        INTO TABLE tbl_Customer
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"' 
        LINES TERMINATED BY ',,,\\r\\n'
        (customer_id, customer_name, customer_email, customer_number, customer_address)";

mysqli_options($con, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_query($con, $sql);
if(mysqli_query($con, $sql)) { 
    echo "insert Successfully";
}else{
    echo "Error ". mysqli_error($con);
}
$rows_imported = mysqli_affected_rows($con); // returns how many rows are affected/inserted
echo $rows_imported.' row(s)affected';

这是我的 csv 文件中的文本：

1, Billy, [e-mail], 0614569631, 20 House Street
2, Steve, [e-mail], 081269691, 13 Curry Street
3, Leo, [e-mail], 0834569692, 7 Pigeon Avenue
4, James, [e-mail], 0714739631, 105 Canary Street
5, Barry, [e-mail], 0821296631, 2759 Kingfisher Street
6, Allen, [e-mail], 0614035631, 19 Bayswater Street
7, Joe, [e-mail], 0845696672, 54 Hamerkop Lane 
8, Oliver, [e-mail], 0834569631, 9 Bedford Street
9, Queen, [e-mail], 0849999631, 2 Albany Street
10, West, [e-mail], 0721398731, 71 Starling Street

标签： phpmysqlcsv