LOAD DATA INFILE error in php

Question

I want to import a csv file content into a database table, I'm using this code that it works perfectly when pasting it in phpmyadmin console:

LOAD DATA LOCAL INFILE '/Applications/MAMP/htdocs/testApp/trips.csv' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS (type,startDate,endDate,steps,coordinates,distance)

However, when using it in a php file, I have an error:

<?php

$host       = "localhost"; //Your database host server
$db         = "dbTest"; //Your database name
$user       = "root"; //Your database user
$pass       = "root"; //Your password
$connection = mysqli_connect($host, $user, $pass);

//Check to see if we can connect to the server
if (!$connection) {
    die("Database server connection failed.");
    die(mysqli_error($db));
} else {
    //Attempt to select the database
    $dbconnect = mysqli_select_db($connection, $db);
    //Check to see if we could select the database
    if (!$dbconnect) {
        die("Unable to connect to the specified database!");
    } else {
        $sql = "LOAD DATA LOCAL INFILE '/Applications/MAMP/htdocs/testApp/trips.csv' 
                INTO TABLE trips
                FIELDS TERMINATED BY ',' 
                LINES TERMINATED BY '\\n' 
               IGNORE 1 ROWS (type,startDate,endDate,steps,coordinates,distance)"
                ;

        $result = mysql_query($sql, $connection);

        if (mysql_affected_rows() == 1) {
            $message = "The trip was successfully inserted!";
        } else {
            $message = "The trip insert failed";
            $message .= mysql_error();
        }

        echo $message;
    }
}
?>

=> The trip insert failed

I'm pretty sure that the problems come from a \ or any other character that I can't target. Thank you for helping .

You might have to escape the `\n` if you want to pass a backslash and an "n" instead of a newline. — Ulrich Eckhardt, Dec 26 '14 at 11:26
Yes, I'm using `\\n` to mean a new line, but perhaps that it's not loading the file. — androniennn, Dec 26 '14 at 11:29
why you are mixing mysql and mysqli functions??? check it carefully — Always Sunny, Dec 26 '14 at 11:30
Try die(dump($sql)); and check what is the $sql before $result = mysql_query($sql, $connection); — Ataboy Josef, Dec 26 '14 at 11:38
For tabular output, the “boxing” around columns enables one column value to be distinguished from another. For nontabular output (such as is produced in batch mode or when the --batch or --silent option is given), special characters are escaped in the output so they can be identified easily. Newline, tab, NUL, and backslash are written as `\n`, `\t`, \0, and \\. The --raw option disables this character escaping. — Ataboy Josef, Dec 26 '14 at 11:44

score 2 · Answer 1 · answered Dec 26 '14 at 12:12

You've mixed up MySQL and MySQLi plugins — you're trying to connect with MySQLi then perform a query with MySQL. You can't do that. Pick one API and use it consistently.

So:

mysql_query($sql)     ---> $connection->query($sql)
mysql_affected_rows() ---> $result->affected_rows()
mysql_error()         ---> $connection->error

Ultimately, this has nothing to do with LOAD DATA INFILE and you should have tried it with a basic SELECT! And I recommend reading the documentation for the functions that you use.

Sorry for that, I'm just struggling to get this work, the back office dev is in holidays. anyway ... — androniennn, Dec 26 '14 at 13:10

Always Sunny · Accepted Answer · 2014-12-27T03:21:00.573

1

EDIT : You are mixing two plugins MYSQL and MYSQLi in same script, below i dumped remaining part of your code using mysqli plugin, because your upper part uses mysqli...

N.B:You can't do that. Pick just any single API.

    $result = mysqli_query($connection, $sql);

    if (mysqli_affected_rows($connection) >= 1) {
        $message = "The trip was successfully inserted!";
    } else {
        $message = "The trip insert failed";
        $message .= mysqli_error($connection);
    }

edited Dec 27 '14 at 03:21

answered Dec 26 '14 at 11:41

Always Sunny

32,751
7
52
86

A good answer explains what is wrong rather than just dumping replacement code. – Lightness Races in Orbit Dec 26 '14 at 12:12
@LightnessRacesinOrbit, i have already commented that answer 45+ mins ago you can see it in comment section, thats why here i just dumped replacement answer....ok never mind for your down vote...mam,best of luck – Always Sunny Dec 26 '14 at 12:15
Comments are insufficient. Comments can be removed at any time. Comments supplement answers, not the other way around. The useful information from comments should be integrated into answers. – Lightness Races in Orbit Dec 26 '14 at 12:16
@LightnessRacesinOrbit, ok i'll try to follow it mam, thanks for advice/suggestion.... – Always Sunny Dec 26 '14 at 12:17
You solved my question dear Always Sunny, and you made my day more sunny! Your replacement code gains me more time, and unfortunately i'm not a php expert. Thank you . – androniennn Dec 26 '14 at 13:53
Congratulations on stealing my answer (basically word for word!) and winning an accept with it. Good job. – Lightness Races in Orbit Dec 27 '14 at 03:16
words are insufficient. words can be removed/change at any time as you said to me previously to my comment @LightnessRacesinOrbit. thanks for down voting my answer. Be broad minded, you already 137k in your bag..... – Always Sunny Dec 27 '14 at 03:20
Not the point. Plagiarism is _not_ cool, and swapping six of my words out for a lightly paraphrased version now that I've called you out on it is not fooling anybody. As for "words can be removed/change[d] at any time", no I didn't say that, I said _comments_ can be. Don't misrepresent me now! – Lightness Races in Orbit Dec 27 '14 at 03:25
i am just trying to mean that, you previously said,"Comments can be removed at any time", then words can also be changed/removed at any time. i am not misrepresenting you, sorry for my bad English. – Always Sunny Dec 27 '14 at 03:33

score -1 · Answer 3 · answered Dec 26 '14 at 11:43

-1

Try to move you trip.csv file into datadir - You can find it with this command : SELECT @ @datadir

answered Dec 26 '14 at 11:43

toto21

602
5
9

LOAD DATA INFILE error in php

3 Answers3