将数据从file.txt传输到数据库中的表时出错

I want to transfer data from a text file: file.txt to a table in a database.

我想将数据从文本文件:file.txt传输到数据库中的表。

Code:

<?php
$host="mysq44.000webhost.com"; // Host name
$user="a203563_user"; // Mysql username
$pass="123omr"; // Mysql password
$db_name="a2031563_global"; // Database name
$tbl_name="table"; // Table name
$file = "file.txt";

$sql=mysql_connect("$host", "$user", "$pass");
if (!$sql) {
    die("Could not connect: " . mysql_error());
}else{
mysql_select_db("$db_name");
$re="LOAD DATA INFILE '$file' INTO TABLE $tbl_name FIELDS TERMINATED BY ',')";
$result = mysql_query("$re");
if (!$result) {
    die("Could not load. " . mysql_error());
}

}
?>

but this is message appears:

但这是消息出现:

Could not load. You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server 
version for the right syntax to use near 'table FIELDS 
TERMINATED BY ',')' at line 1

Can anyone give me some solution or some ideas?

谁能给我一些解决方案或一些想法?

2 个解决方案

#1

The word 'table' is a reserved word in MySQL, you can't call a table, 'table', you'll need to change the name of your table or use backticks ( ` ) around the table name. You also seem to have closing brackets, but no opening ones - you'll want to fix that up to make this work.

单词'table'是MySQL中的保留字,你不能调用表'table',你需要更改表的名称或在表名周围使用反引号(`)。你似乎也有结束括号,但没有开放的 - 你需要修复它才能使它工作。

Just in case you need it, here is the full syntax for the LOAD INFILE command:

万一你需要它,这里是LOAD INFILE命令的完整语法:

LOAD DATA LOCAL INFILE 'somefile.txt' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3);

#2

Your load infile statement have 3 issues:

您的load infile语句有3个问题:

table: is reserved word in MySQL, you need to change table name or inclose inside (``)

table:是MySQL中的保留字,需要更改表名或者包含在里面(``)

remove the extra bracket at end of statement

删除语句末尾的额外括号

The CSV file where you are using to load the data from MUST by owned by MySQL, use command line below:

用于从MySQL拥有的MUST加载数据的CSV文件,使用下面的命令行:
```
 chown mysql.mysql file.txt
```
So in general you need to make your load infile statement like

所以一般来说,你需要使你的负载infile声明像
```
 $re = "LOAD DATA INFILE '$file' INTO TABLE `$tbl_name` FIELDS TERMINATED BY ','";
```

#1