php 读取文本文件并将内容传输到 mysql 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2601349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read a text file and transfer contents to mysql database
提问by Hyman Brown
I need a php script to read a .txt file.
我需要一个 php 脚本来读取 .txt 文件。
The content of the text file are like this:
文本文件的内容是这样的：
data.txt
数据.txt
145|Joe Blogs|17/03/1954
986|Jim Smith|12/01/1976
234|Paul Jones|19/07/1923
098|James Smith|12/09/1998
234|Carl Jones|01/01/1925
These would then get stored into a database like this
然后这些将被存储到这样的数据库中
**DataID |Name |DOB **
**数据 ID |名称 |DOB **
234 |Carl Jones|01/01/1925
I would be so grateful if someone could give me script to achieve this.
如果有人能给我脚本来实现这一点，我将不胜感激。
Update:
更新：
<?
$handle = @fopen("data.txt", "r");
$conn = mysql_connect("localhost","username","password");
mysql_select_db("mydatabase",$conn);
while (!feof($handle)) // Loop til end of file.
{
$buffer = fgets($handle, 4096);
// Read a line.
list($a,$b,$c)=explode("|",$buffer);
//Separate string by the means of |
echo $a."-".$b."-".$c."<br>";
$sql = "INSERT INTO data_table (iddata, name, age) VALUES('".$a."','".$b."',".$c.")";
mysql_query($sql,$conn) or die(mysql_error());
}
?>
get the following error error in your SQL syntax; ...for the right syntax to use near ')' at line 1
在您的 SQL 语法中出现以下错误错误；...在第 1 行的 ')' 附近使用正确的语法
采纳答案by nik
open txt file using fopen:
使用 fopen 打开 txt 文件：
$handle = @fopen("xyz.txt", "r"); //read line one by one
$values='';
while (!feof($handle)) // Loop til end of file.
{
$buffer = fgets($handle, 4096); // Read a line.
list($a,$b,$c)=explode("|",$buffer);//Separate string by the means of |
//values.=($a,$b,$c);// save values and use insert query at last or
// use mysql insert query here
}
THATS IT
就是这样
回答by Anthony Forloney
What you may be looking for is MySQL's built-in function LOAD DATA INFILEto load a text file containing values for a database into a database.
您可能正在寻找的是 MySQL 的内置函数LOAD DATA INFILE，用于将包含数据库值的文本文件加载到数据库中。
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.
LOAD DATA INFILE 语句以非常高的速度将文本文件中的行读取到表中。文件名必须作为文字字符串给出。
Example:
例子：
LOAD DATA INFILE 'data.txt' INTO TABLE my_table;
You could also specify the delimiters inside of your text-file, like so:
您还可以在文本文件中指定分隔符，如下所示：
LOAD DATA INFILE 'data.txt' INTO TABLE my_table FIELDS TERMINATED BY '|';
Update:
更新：
Here is a full-working example, I uploaded a test data file hereand here is my PHP code.
这是一个完整的示例，我在这里上传了一个测试数据文件，这是我的 PHP 代码。
$string = file_get_contents("http://www.angelfire.com/ri2/DMX/data.txt", "r");
$myFile = "C:/path/to/myFile.txt";
$fh = fopen($myFile, 'w') or die("Could not open: " . mysql_error());
fwrite($fh, $string);
fclose($fh);
$sql = mysql_connect("localhost", "root", "password");
if (!$sql) {
die("Could not connect: " . mysql_error());
}
mysql_select_db("my_database");
$result = mysql_query("LOAD DATA INFILE '$myFile'" .
" INTO TABLE test FIELDS TERMINATED BY '|'");
if (!$result) {
die("Could not load. " . mysql_error());
}
Here what the table looked before running my PHP code:
这是运行我的 PHP 代码之前的表格：
mysql> select * from test;
+--------+-----------+------------+
| DataID | Name | DOB |
+--------+-----------+------------+
| 145 | Joe Blogs | 17/03/1954 |
+--------+-----------+------------+
1 row in set (0.00 sec)
And here is the result after:
这是之后的结果：
mysql> select * from test;
+--------+-------------+------------+
| DataID | Name | DOB |
+--------+-------------+------------+
| 145 | Joe Blogs | 17/03/1954 |
| 234 | Carl Jones | 01/01/1925 |
| 98 | James Smith | 12/09/1998 |
| 234 | Paul Jones | 19/07/1923 |
| 986 | Jim Smith | 12/01/1976 |
+--------+-------------+------------+
5 rows in set (0.00 sec)
回答by DixonMD
$sql = "INSERT INTO data_table (iddata, name, age) VALUES('".$a."','".$b."', **'** ".$c." **'** )";
issue is with the single quotes around $c. There is no single quotes. Add it.
问题在于 $c 周围的单引号。没有单引号。添加它。
回答by ziyad
@Anthony ForloneyYour answer is right,just add the vertical bar[ | ] at each very last of each row like this on your data.txtfile...
@ Anthony Forloney您的答案是正确的，只需添加竖线[ | ] 在data.txt文件中每一行的最后一个像这样...
and rest is same as in your answer.This is working for me...
其余与您的回答相同。这对我有用...
dont forget to add the quotes for varchartype for inserting datas to table....
不要忘记为varchar类型添加引号以将数据插入表....
