Importing an CSV to mySQLi


#1

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.

  1. on the LOAD DATA line, it says “unexpected T_STRING”
  2. 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]


#2

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]


#3

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]


#4

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.


#5

Modified again:

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

[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]


#6

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


#7

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]


#8

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 :slight_smile:

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


#9

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.


#10

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”


#11

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


#12

I actually love you.

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

YESSSSSSSSSSSSSSSS.

Thank you SOOOOOOO much for your help.


#13

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]