Importing CSV data using PHP/MySQL - Mysqli syntax

Question

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'

Solution 1

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.

Solution 2

You should probably put guards in to detect when a prepare call fails:

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

Source: http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

I'd also check the path for the LOAD DATA INFILE command. Phpmyadmin might be setting a different path variable. If you try to temporarily put the absolute unix path (and ensure mysql has appropriate permission to that directory), it may clear up the problem.

However, in either case, you should output the error from the mysql client to give you a better idea of what's causing the issue.

Lastly, I'd try to execute the command directly in the MySQL command line client. If there's any "magic" happening in phpmysql or the mysqli php client, it will show up by using the mysql cli client as two of the clients will succeed where one will fail (compared to your assumptions).

I've run into a similiar issue before and it ended up being MySQL's odd way of escaping the delimiter, so I'd look to see that you're escaping the TERMINATED BY parameters correctly.

Comments

[Admin]

I followed you step-by-step and I'm still not there Homer6. Please take a look at my edit.

[Admin]

I followed every step of your answer cerd. I'm indeed getting the same error I read in your box = "This command is not supported in the prepared statement protocol yet". Pls take a look at my edits!

[cerd]

@Amariani I looked into this. See my edited answer above for probable solution.

[Admin]

Bingo! Working like a charm! I wonder how did you figured out that 'Prepared stmts don't support LOAD DATA'. Should I post my final solution as an edit in my question for other victims of LOAD DATA? Im a bit new to this stackflow thing. tks a zillion anyways!

[cerd]

@AMariani - glad that worked. Honestly, I could find no specific references for this error other than comments in the docs from MySQL. You cannot DROP tables either. Perhaps please accept answer for others who may run into this. With mysql_ being deprecated in php 5.5 many others will hit this when going over to mysqli_, PDO etc.

[Homer6]

The first step that I suggested was to output the error message. Which, as it turns out, is exactly what the issue was: "This command is not supported in the prepared statement protocol yetobject(mysqli)#1" Glad you found your issue though.

[Admin]

I had bigger problems using prepare with LOAD DATA, but indeed the your sql string above is the one working!

[cerd]

@Homer6 good call on the above code, I added it into the answer above as a means to illustrate the issue along with the work around.