php - 直接将CSV文件数据插入mysql，

Question

我编写了这段代码，将CSV数据直接插入mysql，但返回总是false。已单独检查了所有功能，但不确定为什么这不起作用。

这是我的代码:-

导入.php

error_reporting(E_ALL);
ini_set('display_errors', 1);

include ('/classes/csvimport.php'); //importing class
require_once ('/classes/db.php');


$connect = new DB(); //connect to database

    $csv = new CSVImport();
        $setencoding = $csv->set_encoding('utf8'); //set database encoding to UTF8

        $csvfile = $csv->addToFiles('csvfile', 'http://pricelists.wave-computers.nl/seTE5yM78eGUSuby.csv'); //convert url path to localfile

        $csv->CSVsettings($csvfile['csvfile']['tmp_name']); //activate default csv setting

        $csv->import();//start import now

        $q1234 = $connect->query("LOAD DATA INFILE 'C:\\Windows\\Temp\\php13CB.tmp' INTO TABLE `wavepricelist` FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '' ESCAPED BY '' IGNORE 1 LINES (`productID`,`brand`,`productName`,`priceSingle`,`inStock`,`EAN`,`vendorID`,`category`,`imageURL`,`weight`)");

        if ($q1234){

            echo "True";
        }
            else 

            echo "False"

csvimport.php

<?php

require_once ('db.php');

class CSVImport
{
  var $table_name; //where to import to
  var $file_name;  //where to import from
  var $use_csv_header; //use first line of file OR generated columns names
  var $field_separate_char; //character to separate fields
  var $field_enclose_char; //character to enclose fields, which contain separator char into content
  var $field_escape_char;  //char to escape special symbols
  var $error; //error message
  var $arr_csv_columns; //array of columns
  var $table_exists; //flag: does table for import exist
  var $encoding; //encoding table, used to parse the incoming file. Added in 1.5 version

    function CSVsettings($file_name="")
    {
        $this->file_name = $file_name;
        $this->arr_csv_columns = array();
        $this->use_csv_header = TRUE;
        $this->field_separate_char = "|";
        $this->field_enclose_char  = "";
        $this->field_escape_char   = "";
        $this->table_exists = TRUE;
        $this->table_name = 'wavepricelist';
    }

    function import()
    {
        if(empty($this->arr_csv_columns))
          $this->get_csv_header_fields();

//      if("" != $this->encoding && "default" != $this->encoding){
//      $this->set_encoding();

        if($this->table_exists)
        {
          $sqlq = "LOAD DATA INFILE '".@mysql_escape_string($this->file_name).
                 "' INTO TABLE `".$this->table_name.
                 "` FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).
                 "' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).
                 "' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).
                 "' ".
                 ($this->use_csv_header ? " IGNORE 1 LINES " : "")
                 ."(`".implode("`,`", $this->arr_csv_columns)."`)";

          $db = new DB ();
          $res = $db->query($sqlq);
            if ($res)
            {
                echo "Executed";
            }
            else
            {
                echo "Error in query";
            }
        }
    }

  //returns array of CSV file columns
    function get_csv_header_fields()
    {
        $this->arr_csv_columns = array();
        $fpointer = fopen($this->file_name, "r");

        if ($fpointer)
        {
          $arr = fgetcsv($fpointer, 10*1024, $this->field_separate_char);

          if(is_array($arr) && !empty($arr))
          {
            if($this->use_csv_header)
            {
              foreach($arr as $val)
                if(trim($val)!="")
                  $this->arr_csv_columns[] = $val;
            }
            else
            {
              $i = 1;
              foreach($arr as $val)
                if(trim($val)!="")
                  $this->arr_csv_columns[] = "column".$i++;
            }
          }
          unset($arr);
          fclose($fpointer);
        }
        else
        {
            $this->error = "file cannot be opened: ".(""==$this->file_name ? "[empty]" : @mysql_escape_string($this->file_name));
        }

            $this->arr_csv_columns = preg_replace('/[^(\x20-\x7F)]*/','', $this->arr_csv_columns);
            return $this->arr_csv_columns;
    }

    //defines the encoding of the server to parse to file
    function set_encoding($encoding)
    {
        //if("" == $encoding)
        //echo "true";
        //$encoding = $this->encoding;
        $sql = "SET SESSION character_set_database = " . $encoding; //'character_set_database' MySQL server variable is [also] to parse file with rigth encoding
        $db = new DB();
        $res = $db->query($sql);
        //return mysqli_error();
        return $encoding;
    }
    /* change end */

    function addToFiles($key, $url)
    {
        $tempName = tempnam('/tmp', 'php_files');
        $originalName = basename(parse_url($url, PHP_URL_PATH));

        $csvRawData = file_get_contents($url);
        file_put_contents($tempName, $csvRawData);

        $_FILES[$key] = array(
            'name' => $originalName,
            'type' =>  'text/csv',
            'tmp_name' => $tempName,
            'error' => 0,
            'size' => strlen($csvRawData)
        );
        return $_FILES;
    }
}
?> 

Answer 1

这里只是猜测，但是...您的 .CSV 文件与数据库位于同一台计算机上吗？

LOAD DATA INFILE 只会从与数据库相同的机器中读取文件。

当您的 PHP 脚本位于与 MySQL 不同的计算机上时，在 PHP 计算机上创建 CSV 文件，然后期望数据库能够读取它，这是一个常见的错误。