Importing an CSV to mySQLi

system · June 17, 2018, 2:36am

I’ve been stuck on this for four days now, so I’m here to seek some help. I’m very new to php and I’ve been trying to import an csv file into my database through code.

on the LOAD DATA line, it says “unexpected T_STRING”
Nothing ever uploads to SQL, there’s 100% no connections issues though.

This is my code, what am I doing wrong?:

[php] <?php

$servername = “localhost”;
$username = “user”;
$password = “password”;
$dbname = “mattsDB”;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error); }

LOAD DATA LOCAL INFILE (‘hi.csv’)
INTO TABLE names
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(firstname);

?>
[/php]

astonecipher · June 17, 2018, 3:46am

Where did you get this bit,

[php]LOAD DATA LOCAL INFILE (‘hi.csv’)
INTO TABLE names
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(firstname);[/php]

JimL · June 17, 2018, 6:01am

This is SQL, not PHP.

[php]LOAD DATA LOCAL INFILE (‘hi.csv’)
INTO TABLE names
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(firstname);[/php]

In your code you set up a connection to sql but then seem to assume you can write SQL code in your PHP script, you will have to send your SQL query to the db as a string like this. Do note that you might have to do something about the csv file path as mysql might not be looking at that folder (you might be better off using the full path to the file)

[php]
$query = “LOAD DATA …”;

if ($mysqli->query($query) === TRUE) {
// data imported
}[/php]

Matt1966 · June 17, 2018, 1:07pm

Hi guys, I opted to make an account here as it seems like a warm place to learn PHP.

Thank you for taking a look at my issue.

Where did you get this bit,

I originally got that code from stackoverflow.

This is currently what I have (which gives off no errors… wooop)

[php]

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "namesdb"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $query = "LOAD DATA LOCAL INFILE ('hi.csv') INTO TABLE names FIELDS TERMINATED BY ',' ENCLOSED BY '".chr(34)."' LINES TERMINATED BY '\n' IGNORE 1 ROWS (firstname)"; if ($mysqli->query($query) === TRUE) { // data imported } ?>

[/php]

In “// data imported” I’m not too sure what is supposed to be there…sorry If I’m seeming a bit slow here

Thank you guys, really do appreciate it.

EDIT: I originally thought it was a filepath issue, so I made a seperate page and echoed out the csv, and it echoed it out perfectly so it’s finding the file for sure.

Matt1966 · June 17, 2018, 2:19pm

Modified again:

What’s worrying… it doesn’t say success, OR error…

[php]
$servername = “localhost”;
$username = “user”;
$password = “password”;
$dbname = “namesdb”;

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error); }

$query =
“LOAD DATA LOCAL INFILE (‘hi.csv’)
INTO TABLE names
FIELDS TERMINATED BY ‘,’
ENCLOSED BY '”.chr(34)."’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(firstname)";

if ($mysqli->query($query) === TRUE) {
echo “New record created successfully”;
} else {
echo "Error: " . $sql . “
” . mysqli_error($conn);
}
[/php]

JimL · June 17, 2018, 4:32pm

Is your dev environment set up to display all errors? You might be getting an internal server error which is shown as a blank page by default

Matt1966 · June 17, 2018, 5:09pm

Thanks for your response.

Ok, I did a bit of digging and managed to turn errors on.

These are the errors which display:

“Notice: Undefined variable: mysqli in /home/maffews/public_html/csv-test.php on line 32”

“Fatal error: Uncaught Error: Call to a member function query() on null in /home/maffews/public_html/csv-test.php:32 Stack trace: #0 {main} thrown in /home/maffews/public_html/csv-test.php on line 32”

Line 32 is:
[php]
if ($mysqli->query($query) === TRUE) {
[/php]

JimL · June 17, 2018, 5:23pm

That’s kinda my fault I guess. You don’t call your Mysqli connection $mysqli… You call it $conn. Note that it doesn’t matter what you call it, but you need to use the same variable name for the query as you set above

Good thing you got the error situation sorted, stumbling around in the blind when coding is practically impossible

Matt1966 · June 17, 2018, 5:45pm

Thanks for your response again!

I thought I might’ve had it then, then booom! new error… ugh…

This is the error(s)
" Notice: Undefined variable: sql in /home/maffews/public_html/csv-test.php on line 35 " line 35 = [php] echo "Error: " . $sql . “
” . $conn->error;[/php]

“Error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(‘hi.csv’) INTO TABLE names FIELDS TERMINATED BY ‘,’ ENCLOSED BY '”’ LINES T’ at line 1"

Ok so this is my current code:

[php]

<?php $servername = "localhost"; $username = "user"; $password = "password"; $dbname = "mattsDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $query = "LOAD DATA LOCAL INFILE ('hi.csv') INTO TABLE names FIELDS TERMINATED BY ',' ENCLOSED BY '".chr(34)."' LINES TERMINATED BY '\n' IGNORE 1 ROWS (firstname)"; if ($conn->query($query) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "
" . $conn->error; } $conn->close(); ?>

[/php]

This is starting to look like chinese to me now haha.

Matt1966 · June 17, 2018, 6:22pm

I’ve attempted to use my brain and figure it out… may be wrong though:

[php]

if ($conn->query($query) === TRUE) {
echo “New record created successfully”;
} else {
echo "Error: " . $query . “
” . $conn->error;
}

$conn->close();

[/php]

This also errors, but it’s the actual error: message.
“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(‘hi.csv’) INTO TABLE names FIELDS TERMINATED BY ‘,’ ENCLOSED BY '”’ LINES T’ at line 1" so it’s triggering the “else”

JimL · June 17, 2018, 6:26pm

Again you do not have that $sql variable, it’s probably meant to add your query there to give the error message context.

You do get the full error message though! Your SQL doesn’t validate, when checking the LOAD DATA INFILE page at the docs it doesn’t seem like there should be parenthesis around the file name
https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Matt1966 · June 17, 2018, 6:32pm

I actually love you.

YES! YES YES YES YES YES YES!!!
FIXED!

YESSSSSSSSSSSSSSSS.

Thank you SOOOOOOO much for your help.

Matt1966 · June 17, 2018, 6:38pm

For future reference for anybody else…

This code works:

[php]

<?php //error reporting ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL); $servername = "localhost"; $username = "user"; $password = "pass"; $dbname = "namesdb"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $query = "LOAD DATA LOCAL INFILE 'hi.csv' INTO TABLE names FIELDS TERMINATED BY ',' ENCLOSED BY '".chr(34)."' LINES TERMINATED BY '\n' IGNORE 1 ROWS (firstname)"; if ($conn->query($query) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $query . "
" . $conn->error; } $conn->close(); ?>

[/php]