php - PHP 代码或 CSV 文件缺少什么？

Question

'LINES TERMINATED BY' 问题 - PHP、SQL 数据库、CSV 文件'

我正在学习如何使用 PHP 从本地文件更新数据库。它工作正常，只是我的 CSV 文件中的最后一个条目被插入到数据库表中的所有字段中。

我已经尝试用 HEX 检查 CSV，以及所有可以想到的 LINES TERMINATED BY(\r\n、\n 等)版本。

这是我的 CSV file

此图显示代码已正确执行。
https://imgur.com/p13OAoj

这是我的 PHP 代码:

$dbhost = 'hidden';
$dbuser = 'hidden';
$dbpass = 'hidden';
$dbname = 'hidden';

// connect to the database
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);

// check connection
if(!$conn){
    echo 'Connection error: '. mysqli_connect_error();
}

// create temporary table
$create = 'CREATE TABLE tmp
(
   name_id varchar(255),
   phone varchar(255),
   INDEX (name_id)
)'; 

// check if creation was successful
if(mysqli_query($conn, $create)){
    echo "Records were updated successfully CREATE.";
} else {
    echo "ERROR: Could not able to execute $create. " . mysqli_error($conn);
} ?><br><br><?php

// load data from local file into temp table
$load = "LOAD DATA LOCAL INFILE 'try1.csv' 
INTO TABLE tmp
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(@name_id, @phone)" ;

// check if load was successful
if(mysqli_query($conn, $load)){
    echo "Records were updated successfully LOAD.";
} else {
    echo "ERROR: Could not able to execute $load. " . mysqli_error($conn);
} ?><br><br><?php

// set/update temp table with info from csv file
$set = "UPDATE tmp SET
name_id = @name_id,
phone = @phone  "
;

// check if update was successful
if(mysqli_query($conn, $set)){
    echo "Records were updated successfully SET.";
} else {
    echo "ERROR: Could not able to execute $set. " .   mysqli_error($conn);
}


mysqli_close($conn); 

我希望 CSV 文件中的所有 4 个条目都被插入，但我得到的是数据库中所有 4 个条目的最后一行值。

这张图片显示了我的数据库表中的结果。
https://i.imgur.com/2lKB2Ix.png

Answer 1

您需要在代码中修改两件事:

1. 如果您刚刚添加了数据，则无需更新数据。
2. 加载数据中的列名称不正确(您应该删除 @)，因此您插入了空行。 (如果你的表中只有这两列，你不需要指定名称，所以你也可以删除它们。)

将您的代码修改为:

$dbhost = 'hidden';
$dbuser = 'hidden';
$dbpass = 'hidden';
$dbname = 'hidden';

// connect to the database
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);

// check connection
if(!$conn){
    echo 'Connection error: '. mysqli_connect_error();
}

// create temporary table
$create = 'CREATE TABLE tmp
(
   name_id varchar(255),
   phone varchar(255),
   INDEX (name_id)
)'; 

// check if creation was successful
if(mysqli_query($conn, $create)){
    echo "Records were updated successfully CREATE.";
} else {
    echo "ERROR: Could not able to execute $create. " . mysqli_error($conn);
} ?><br><br><?php

// load data from local file into temp table
$load = "LOAD DATA LOCAL INFILE 'try1.csv' 
INTO TABLE tmp
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(name_id, phone)" ;

// check if load was successful
if(mysqli_query($conn, $load)){
    echo "Records were updated successfully LOAD.";
} else {
    echo "ERROR: Could not able to execute $load. " . mysqli_error($conn);
} ?><br><br><?php


mysqli_close($conn);