使用PHP / MySQL导入CSV数据 - Mysqli语法 [英] Importing CSV data using PHP/MySQL - Mysqli syntax

问题描述

在此问题的底部最终工作的最终代码！

尝试实现此操作（使用PHP / MySQL导入CSV数据）。我必须几乎在那里...

notes1：我的$ sql直接从复制/粘贴phpmyadmin（生成PHP代码），并运行在phpmyadmin。 p>

note2：如果我注释行$ sql =DELETE FROM dbase，代码运行正常（清除表）。

所以如果我知道我的sql是正确的，我的代码可以运行其他sqls，为什么下面不运行？ Im get：

在非对象上调用成员函数execute（） - 对于

  $ stmt-> execute（）; 
  

完整代码：

 <？php 
 $ mysqli = new mysqli（'localhost'，'root'，'pass'，'dbase'） 
 / *检查连接* / 
 if（mysqli_connect_errno（））{
 printf（Connect failed：％s\\\
，mysqli_connect_error（））; 
 exit（）; 
} 
 
 $ sql =LOAD DATA INFILE \'./ myfile.csv \'INTO TABLE tab \\\

。 FIELDS TERMINATED BY \'，\'\ n
。 LINES TERMINATED BY \'\\r\\\\
\'\\\

。 IGNORE 1 LINES; 
 
 // $ sql =DELETE FROM dbase; 
 
 $ stmt = $ mysqli-> prepare（$ sql）; 
 $ stmt-> execute（）; 
 $ stmt-> close（）; 
 $ mysqli-> close（）; 
？> 
  

提前提交！

更改后仍然无法使用！

新代码：

 <？php 
 
 $ mysqli = new mysqli（'localhost'，'root'，'pass'，'dbase'）; 
 / *检查连接* / 
 if（mysqli_connect_errno（））{
 printf（Connect failed：％s\\\
，mysqli_connect_error（））; 
 exit（）; 
} 
 
 / *当前默认数据库的返回名* / 
 if（$ result = $ mysqli-> query（SELECT DATABASE（）））{
 $ row = $ result-> fetch_row（）; 
 printf（默认数据库是％s.\\\
，$ row [0]）; 
 $ result-> close（）; 
} 
 
 $ sql =LOAD DATA INFILEC：/xampp/htdocs/myfile.csv'INTO TABLE标签
由'，'
终止的字段由'\\r\\\\
'
 IGNORE 1 LINES'终止; 
 
 echo< br>; 
 echo< br>; 
 echo $ sql 
 echo< br>; 
 echo< br>; 
 $ stmt = $ mysqli-> prepare（$ sql）; 
 
 / *准备语句，阶段1：准备* / 
 if（！（$ stmt = $ mysqli-> prepare（$ sql）））
 {echoPrepare失败：（。$ mysqli-> errno。）。 $ mysqli-> error; 
} 
 
 
 //注意这里我们抛弃我们的OBJ看到它是
 //创建和准备状态，然后杀死脚本
 var_dump（$ mysqli）; 
 exit（）; 
 
 // $ sql =DELETE FROM intrasdump 
 
 $ stmt = $ mysqli-> prepare（$ sql）; 
 $ stmt-> execute ）; 
 $ stmt-> close（）; 
 $ mysqli-> close（）; 
？> 
  

我在浏览器中看到的是以下内容：

默认数据库是dbname。 p>

加载数据INFILE'C：/xampp/htdocs/myfile.csv'INTO TABLE选项卡由''''终止的线，''\r\\\
'IGNORE 1 LINES

准备失败：（1295）准备语句中不支持此命令yetobject（mysqli）＃1（19）{[affected_rows] => int （-1）[client_info] => string（79）mysqlnd 5.0.11-dev - 20120503 - $ Id：40933630edef551dfaca71298a83fad8d03d62d4 $[client_version] => int（50011）[connect_errno] => int （error_list）。这个命令在预准备的语句协议中不被支持。 ] => array（0）{} [field_count] => int（1）[host_info] => string（20）localhost via TCP / IP[info] => NULL [insert_id ] => int（0）[server_info] => string（6）5.6.11[server_version] => int（50611） ：2问题：865慢查询：0打开：75刷新表：1打开表：68每秒查询平均值：0.108[sqlstate] =>字符串（5）00000[protocol_version] => int 10）[thread_id] => int（117）[warning_count] => int（0）}

注意：如果我复制粘贴sql字符串echoed以上在mysql提示符下，它运行就好了。这应该意味着文件位置问题和sql字符串本身都很好，不是

怎么这么难？！

编辑3。

为所有答案和评论。最终代码版本如下：

 <？php 
 
 $ mysqli = new mysqli '，'root'，'pass'，'dbname'）; 
 / *检查连接* / 
 if（mysqli_connect_errno（））{
 printf（Connect failed：％s\\\
，mysqli_connect_error（））; 
 exit（）; 
} 
 
 $ sql =LOAD DATA INFILEC：/xampp/htdocs/myfile.csv'INTO TABLE标签
由'，'
终止的字段由'\\r\\\\
'
 IGNORE 1 LINES'终止; 
 
 //尝试执行查询（不是stmt）并捕获mysqli错误从引擎和php错误
如果（！$ stmt = $ mysqli->查询（$ sql））） {
 echo\\\
Query execute failed：ERRNO：（。$ mysqli-> errno。）。 $ mysqli-> error; 
}; 
？> 
  

有用的注释：

• 请注意，文件路径使用 frw-slash 而不是windows默认反斜杠。顺序只会注意工作。上帝知道我是如何想出一个...




• 利用答案中提供的许多调试代码。我想一个有效的方法来检查您的sql是否是正确的回声（ echo $ sql ）它和复制/粘贴在您的SQL提示符。不信任phpmyadmin的创建PHP PHP代码功能。




• 请注意'准备的帖子不支持LOAD DATA'

解决方案

编辑可能的解决方案：准备的stmts不支持 LOAD DATA strong>

如果您使用 mysqli_query - 而不是 mysqli-> code> ..-> execute（）;这应该工作。

因此：

  //以上正常连接
 $ sql =LOAD DATA INFILE/myfile.csv'INTO TABLE选项卡
。 FIELDS TERMINATED BY'，'
。 LINES TERMINATED BY'\r\\\
'
。 IGNORE 1 LINES; 
 
 // $ sql =DELETE FROM dbase; 
 // $ stmt = $ mysqli-> query（$ sql）; 
 //集成其他海报好recc捕获错误：
 
 //尝试执行查询（不是stmt）并捕获mysqli错误从引擎和php错误
 if（！ $ stmt = $ mysqli-> query（$ sql）））{
 echo\\\
Query execute failed：ERRNO：（。$ mysqli-> errno。）。 $ mysqli-> error; 
} 
  

有用的还是var_dump（$ mysqli）因为在我的env中我们不允许LOAD FILE，但这告诉我引擎成功解析并尝试执行查询。

你需要得到更好的错误信息，位

诊断过程，以获得此点：

  $ sql =LOAD DATA INFILE ...; 
 echo $ sql; 
 $ stmt = $ mysqli-> prepare（$ sql）; 
 
 //注意这里我们抛弃我们的OBJ看到它是
 //创建和准备状态，然后杀死脚本
 var_dump（$ mysqli）; 
 exit（）; 
  

我的盒子上的结果（不是最佳表示）： >

  object（mysqli）＃1（18）{
 ... 
 string（68）在
预备语句协议中不支持
  

2。您将看到的其他可能错误

FILE私人资料不足或档案地址不正确目录 b $ b

phpMyAdmin是很漂亮的沙箱，并且将完全不同于mysqli / php。

从MySQL文档处理以下内容，并阅读本节。像我说的，LOAD..FILE是一个非常敏感的操作，有很多限制。 LOAD ... FILE MySQL文档

出于安全考虑，当读取位于服务器上的文本文件时，
文件必须驻留在数据库目录或可读的
。此外，要在服务器文件上使用LOAD DATA INFILE，必须具有
FILE权限。请参见第6.2.1节MySQL提供的特权。
对于非本地加载操作，如果secure_file_priv系统变量
设置为非空目录名，则要加载的文件必须是位于该目录中的
。

IN THE BOTTOM OF THIS QUESTION THE FINAL CODE THAT FINALLY WORKED!

Trying to implement this (Importing CSV data using PHP/MySQL). I must be almost there...

notes1: my $sql came straight from copy/paste phpmyadmin (generate php code) and ran just fine in the phpmyadmin.

note2: If I comment the line $sql="DELETE FROM dbase" the code runs just fine (and the table is cleaned).

So if i know my sql is right and my code can run other sqls, why does the below does not run?! Im getting:

Call to a member function execute() on a non-object - for the line

$stmt->execute();

Full code:

<?php
$mysqli  =  new mysqli('localhost','root','pass','dbase');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$sql = "LOAD DATA INFILE \'./myfile.csv\' INTO TABLE tab\n"
. " FIELDS TERMINATED BY \',\'\n"
. " LINES TERMINATED BY \'\\r\\n\'\n"
. " IGNORE 1 LINES";

//$sql="DELETE FROM dbase";

$stmt=$mysqli->prepare($sql);
$stmt->execute(); 
$stmt->close();
$mysqli->close();
?>

tks in advance!

EDIT:

Made below changes and still not working!

new code:

<?php

$mysqli  =  new mysqli('localhost','root','pass','dbase');
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* return name of current default database */
if ($result = $mysqli->query("SELECT DATABASE()")) {
    $row = $result->fetch_row();
    printf("Default database is %s.\n", $row[0]);
    $result->close();
}

$sql = "LOAD DATA INFILE 'C:/xampp/htdocs/myfile.csv' INTO TABLE tab
        FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\\r\\n'
    IGNORE 1 LINES";

echo "<br>";
echo "<br>";
echo $sql;
echo "<br>";
echo "<br>";
$stmt=$mysqli->prepare($sql);

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare($sql))) 
{    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}


// NOTE HERE WE'RE DUMPING OUR OBJ TO SEE THAT IT WAS 
// CREATED AND STATUS OF PREPARE AND THEN KILLING SCRIPT   
var_dump($mysqli);
exit();

//$sql="DELETE FROM intrasdump

$stmt=$mysqli->prepare($sql);
$stmt->execute(); 
$stmt->close();
$mysqli->close();
?>

What i see in my browser when I ran this is the following:

Default database is dbname.

LOAD DATA INFILE 'C:/xampp/htdocs/myfile.csv' INTO TABLE tab FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES

Prepare failed: (1295) This command is not supported in the prepared statement protocol yetobject(mysqli)#1 (19) { ["affected_rows"]=> int(-1) ["client_info"]=> string(79) "mysqlnd 5.0.11-dev - 20120503 - $Id: 40933630edef551dfaca71298a83fad8d03d62d4 $" ["client_version"]=> int(50011) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(1295) ["error"]=> string(68) "This command is not supported in the prepared statement protocol yet" ["error_list"]=> array(0) { } ["field_count"]=> int(1) ["host_info"]=> string(20) "localhost via TCP/IP" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(6) "5.6.11" ["server_version"]=> int(50611) ["stat"]=> string(133) "Uptime: 7993 Threads: 2 Questions: 865 Slow queries: 0 Opens: 75 Flush tables: 1 Open tables: 68 Queries per second avg: 0.108" ["sqlstate"]=> string(5) "00000" ["protocol_version"]=> int(10) ["thread_id"]=> int(117) ["warning_count"]=> int(0) }

Note: If I copy paste the sql string echoed above in to mysql prompt, it runs just fine. That should mean that both the file location issue and the sql string itself are fine, no???

how can this be so hard?!

EDIT 3.

Tks for all answers and comments. Final code version below works:

<?php

$mysqli  =  new mysqli('localhost','root','pass','dbname');
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql = "LOAD DATA INFILE 'C:/xampp/htdocs/myfile.csv' INTO TABLE tab
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\\r\\n'
        IGNORE 1 LINES";

//Try to execute query (not stmt) and catch mysqli error from engine and php error
if (!($stmt = $mysqli->query($sql))) {
    echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
};
?>

useful notes:

• note the file path uses frw-slash instead of windows-default back-slash. Orderwise will just note work. God knows how I figured that one out...

• take advantage of the many debugging codes offered in the answers. i guess one effective way to check if your sql is right to echo (echo $sql) it and copy/paste in your sql prompt. don't trust phpmyadmin 'create php PHP code' functionality.

• keep in mind 'Prepared stmts don't support LOAD DATA'

解决方案

EDIT for Probable Solution: Prepared stmts don't support LOAD DATA.

If you use mysqli_query - instead of mysqli->prepare ..->execute(); this should work.

So:

//Connect as normal above
$sql = "LOAD DATA INFILE '/myfile.csv' INTO TABLE tab"
. " FIELDS TERMINATED BY ','"
. " LINES TERMINATED BY '\r\n'"
. " IGNORE 1 LINES";

//$sql="DELETE FROM dbase";
// $stmt = $mysqli->query($sql);
// Integrate other posters good recc to catch errors:

//Try to execute query (not stmt) and catch mysqli error from engine and php error
if (!($stmt = $mysqli->query($sql))) {
    echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
}

useful still to var_dump($mysqli) here to see result I got access denied as in my env we disallow LOAD FILE, but that tells me the engine successfully parsed and attempted to execute the query.

You need to get better error info, there is a bit that could be going on, here is how I would dig in:

Diagnosis Process to get to this Point:

$sql = "LOAD DATA INFILE ...";
echo $sql;
$stmt=$mysqli->prepare($sql);

// NOTE HERE WE'RE DUMPING OUR OBJ TO SEE THAT IT WAS 
// CREATED AND STATUS OF PREPARE AND THEN KILLING SCRIPT   
var_dump($mysqli);
exit();

Results on my Box (not the best representation):

  object(mysqli)#1 (18) {
  ...
  string(68) "This command is not supported in the 
              prepared statement protocol yet"

2. Other possible errors you would see

Insufficient FILE privs or Improper Directory of file Loc

phpMyAdmin is pretty sandboxed and will work completely differently than mysqli/php.

Address the following from MySQL Docs, and read up on this section. Like I said, LOAD..FILE is a very sensitive operation with a lot of restrictions. LOAD...FILE MySQL Docs

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 6.2.1, "Privileges Provided by MySQL". For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

这篇关于使用PHP / MySQL导入CSV数据 - Mysqli语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！