php - 将 CSV 值插入数据库时​​，列计数与第 1 行错误的值计数不匹配

Question

我试图在 php 中解析一个 csv 文件，然后将结果插入到 mysql 数据库中。我有以下代码，当我上传文件时出现 MYSQL 错误::列计数与第 1 行的值计数不匹配。我不知道发生了什么。

require "./classfun.php";
require_once "./mydb.php";
printDocHeading("./index.css", "parser");
if (empty($_POST)) {
    print "<div class=content>";
    ShowForm();
    print "</div>";
} else if ($_POST['submit']) {
    print "<div class=content>";
    CSVImport();
    print "</div>";
}
//////////////////////////////////////////////////////////////////////////////////
function ShowForm()
{
    print "<form method='post' action='$_SERVER[PHP_SELF]' enctype='multipart/form-data'>\n";
    print "<p>enter csv file:\n" . "<input type='file' name ='csv' value=''/>\n" . "</p>" . "<input type='submit' name='submit' />" . "</p>";
}
function CSVImport()
{
    if ($_FILES['csv']['error'] == 0) {
        $name    = $_FILES['csv']['name'];
        $ext     = strtolower(end(explode('.', $_FILES['csv']['name'])));
        $type    = $_FILES['csv']['type'];
        $tmpName = $_FILES['csv']['tmp_name'];
        print "name: $name<br >";
        print "extenstion: $ext<br >";
        print "type: $type<br >";
        print "name: $tmpName<br >";
        // parsing begins
        if ($ext === 'csv') {
            if (($handle = fopen($tmpName, 'r')) !== FALSE) {
                $tables    = "'metaData', 'campaignAssoc'";
                $row_count = 0;
                $sql_query = "INSERT INTO meta (" . implode(metaNo, metaData, campaignAssoc) . ") VALUES('',";

                $rows = array();
                //Read the file as csv
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $row_count++;
                    foreach ($data as $key => $value) {
                        $data[$key] = "'" . addslashes($value) . "'";
                    }
                    $rows[] = implode(",", $data);
                }
                $sql_query .= implode("),(", $rows);
                $sql_query .= ", '2')";
                fclose($handle);

                if (count($rows)) { //If some recores  were found,
                    //Replace these line with what is appropriate for your DB abstraction layer
                    mysql_connect('-------------------------') or die(mysql_error());
                    mysql_select_db('mthurin');
                    //mysql_query("TRUNCATE TABLE meta") or die("MySQL Error: " . mysql_error()); //Delete the existing records
                    mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.

                    print 'Successfully imported ' . $row_count . ' record(s)';
                } else {
                    print 'Cannot import data - no records found.';
                }
            }
        }
    }
}

有什么建议吗？

Answer 1

该错误意味着与查询中的列数相比，您查询的 VALUES 节中的项目太多。

下面是一个会触发相同错误的查询示例:

INSERT INTO some_table (a,b,c) VALUES (1,2,3,4)

正确的查询应该是:

INSERT INTO some_table (a,b,c,d) VALUES (1,2,3,4)

或者:

INSERT INTO some_table (a,b,c) VALUES (1,2,3)

调试问题的最简单方法是打印您发送到数据库的查询，并确保列数与值数匹配。