迭代文件夹中的CSV文件，并使用PHP将数据加载到MySQL中

I use the following code to iterate through the files in a folder and have the data loaded onto a MYSQL table. I couldn't quite get this working. Is there any better solution?

我使用以下代码迭代文件夹中的文件,并将数据加载到MYSQL表。我无法完成这项工作。有没有更好的解决方案?

if ($handle = opendir('C:\Documents and Settings\Desktop\CSV Files')) {
while(false !== ($entry = readdir($handle)))
{
mysql_query("LOAD DATA LOCAL INFILE %handle
                          INTO TABLE main_table
                          FIELDS
                          TERMINATED BY ','
                          OPTIONALLY ENCLOSED BY '"'
                          ESCAPED BY '"'
                          LINES TERMINATED BY '\r\n'
                          IGNORE 1 LINES")
}
}

1 个解决方案

#1

There is so much wrong with your code it's hard to pick where to begin...

你的代码有很多错误,很难选择从哪里开始......

Your directory path is not escaped.

您的目录路径未转义。

You do not have anything to tell you that the directory cannot be opened.

您没有任何要告诉您的目录无法打开。

%handle is not a PHP variable, and it's the wrong variable besides.

%handle不是PHP变量,除此之外它是错误的变量。

$entry is just the filename, not the full path.

$ entry只是文件名,而不是完整路径。

there's no semicolon at the end of the mysql_query() call.

在mysql_query()调用结束时没有分号。

Your 'enclose' and 'escape' chars are both ", neither of which were escaped.

你的'封闭'和'逃脱'字符都是“,两者都没有逃脱。

echo the query instead of running it to be sure it's formed correctly if it's not working.

回显查询而不是运行它,以确保它正确形成,如果它不工作。

You never actually check to see if the query was successful.

您实际上从未检查过查询是否成功。

turn on error reporting, because you've got some glaring parse errors that should make this script fail before it even runs.

打开错误报告,因为你有一些明显的解析错误,应该使这个脚本在运行之前失败。

and the ever-popular: mysql_* functions are deprecated, you should be using PDO or MySQLi.

并且不受欢迎的:mysql_ *函数已被弃用,您应该使用PDO或MySQLi。

So this should probably work a:

所以这可能应该是:

<?php
error_reporting(E_ALL);
define('DEBUG', true);

$basedir = 'C:\\Documents and Settings\\Desktop\\CSV Files\\';
if ($handle = opendir($basedir)) {
  while(false !== ($entry = readdir($handle))) {
    $query = "LOAD DATA LOCAL INFILE $basedir$entry
                INTO TABLE main_table
                FIELDS
                TERMINATED BY ','
                OPTIONALLY ENCLOSED BY '\"'
                ESCAPED BY '\\'
                LINES TERMINATED BY '\r\n'
                IGNORE 1 LINES");
    if(DEBUG) { echo $query . "\n"; }
    if(!mysql_query($query)) {
      die('Query failed: ' . mysql_error());
    }
  }
} else {
  echo "Could not open $basedir";
}

#1