找到你要的答案

Q:Error on transfering data from file.txt to a table in a database

Q:在数据传输file.txt来从数据库中的表的误差

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

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?

我想从一个文本文件传输数据:在一个数据库中的表file.txt。

代码:

<?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());
}

}
?>

但这是信息出现:

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

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

answer1: 回答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.

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

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);

“表”是MySQL的一个保留字,你可以不打表,“表”,你需要改变你的名字的表或使用引号(`)在表名。你似乎也有关闭的括号,但没有开放的-你会想修复,使这项工作。

以防万一你需要它,这是负载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);
answer2: 回答2:

Your load infile statement have 3 issues:

  1. table: is reserved word in MySQL, you need to change table name or inclose inside (``)
  2. remove the extra bracket at end of statement
  3. The CSV file where you are using to load the data from MUST by owned by MySQL, use command line below:

     chown mysql.mysql file.txt
    

    So in general you need to make your load infile statement like

     $re = "LOAD DATA INFILE '$file' INTO TABLE `$tbl_name` FIELDS TERMINATED BY ','";
    

你有3个问题负荷infile语句:

  1. table: is reserved word in MySQL, you need to change table name or inclose inside (``)
  2. remove the extra bracket at end of statement
  3. 在你使用CSV文件加载数据从必须拥有的MySQL,使用命令行下面:

     chown mysql.mysql file.txt
    

    所以你需要让你的负荷infile语句一样

     $re = "LOAD DATA INFILE '$file' INTO TABLE `$tbl_name` FIELDS TERMINATED BY ','";
    
php  mysql