Uploading CSV file into mysql database using PHP

Question

I am having a problem uploading a CSV file into my MySQL database. I'm not a very experienced coder so sorry if my attempt at solving the problem is way off, but I am going to include what I have tried so far.

Here is the HTML portion of the code:

<form enctype="multipart/form-data" action="camperUpload.php" method="POST">
<input type="hidden" name="MAX_FILE_SIZE" value="100000" />
Choose a file to upload: <input name="uploadedfile" type="file" /><br />
<input type="submit" value="Upload File" class="btn btn-border" />
</form>

And here is the PHP portion:

function processFile($uploadedFile) {



    // file contents
    $file_contents = $uploadedFile["tmp_name"];

    $SQL_statement = "LOAD DATA LOCAL INFILE '$file_contents' INTO TABLE C_CAMPER FIELDS TERMINATED BY ','
                        LINES TERMINATED BY '\n' IGNORE 3 LINES
                        (camperFName,campLName,camperAddress,camperCity,camperZip,camperCountry,camperPhone,camperEmail,@camperAltEmail,@camperDOB,camperAge,camperSchool,camperGradYear,camperGPA,camperACT/SAT,camperPar,camperParPhone,camperPrimPos,camperSecPos);";
SET camperDOB = STR_TO_DATE(@camperDOB, '%b-%d-%Y'),
    camperAltEmail = NULLIF(@camperAltEmail, 'null');"
    // Run SQL query
    $DB->execute($SQL_statement);

The top two lines in the CSV file are not important, and there is one line that is the headers, so that's why I have it skipping the first three lines. The date format in the file is 6/5/1999, so I assumed I had to try to change that. I put "LINES TERMINATED BY '\n' IGNORE 3 LINES" but I'm not sure if I should even have that, the lines aren't terminated by anything.

I am using phpMyAdmin for my database, in case that is important.

When I try to execute this, it only goes to a page that says the page isn't working... so I know I'm probably way off or missing things. Any help would be appreciated, thanks!

EDIT: Here is my PHP file with the changes suggested by Vasiliy Zverev. Now it is not working because apparently phpMyAdmin has problems with the LOAD DATA LOCAL INFILE command?

 <?php
    // version 1.02
    // display errors for debugging
    ini_set("display_errors", true);
    error_reporting(E_ALL);

    // Open a connection to the SQL server so we can run queries later. 
    $conn = new mysqli(removed for privacy); // DON'T FORGET TO EDIT THIS PART!
    // Output error info if there was a connection problem
    if ($conn->connect_errno) {
        die("<h3> Uh oh! It looks like we're having trouble connecting to the 
 website at the moment. Try again soon! {$conn->connect_error}</h3>");
    }

    // file name
    $file_contents = $_FILES["uploadedfile"]["tmp_name"];

    $SQL_statement = "LOAD DATA LOCAL INFILE '$file_contents' INTO TABLE C_CAMPER FIELDS TERMINATED BY ','
                        LINES TERMINATED BY '\n' IGNORE 3 LINES
                        (camperFName,campLName,camperAddress,camperCity,camperZip,camperCountry,camperPhone,camperEmail,@camperAltEmail,@camperDOB,camperAge,camperSchool,camperGradYear,camperGPA,`camperACT/SAT`,camperPar,camperParPhone,camperPrimPos,camperSecPos)
                        SET camperDOB = STR_TO_DATE(@camperDOB, '%m/%d/%Y'),
                            camperAltEmail = NULLIF(@camperAltEmail, 'null');";
    // Run SQL query
    if( !$conn->query($SQL_statement)) {
        echo $conn->error;
    }

    // Close the SQL connection
    $conn->close();
?>

The line starting with SET camperDOB is not a part of the string $SQL_statement. Turn PHP error reporting on. The error messages will show you what is breaking. — Sloan Thrasher, Apr 09 '17 at 19:14
Also, your SET statement should be an update since it will execute after the LOAD DATA. — Sloan Thrasher, Apr 09 '17 at 19:15
`global $DB;` why is that defined as global? Are you referring to the connection? It looks to me that you're using the wrong variable here and your edit and original code contain syntax errors. — Funk Forty Niner, Apr 09 '17 at 21:03
`$uploadedFile` where is that defined? You're looking to get that from an uploaded file; what you're using won't work. — Funk Forty Niner, Apr 09 '17 at 21:04
@Fred-ii- I updated my edit in the original post. Where do I need to define $uploadedFile? — Jacobra, Apr 09 '17 at 21:08
Have a look through this on php.net http://php.net/manual/en/features.file-upload.post-method.php — Funk Forty Niner, Apr 09 '17 at 21:13

Vasiliy Zverev · Accepted Answer · 2017-04-09T20:09:17.537

0

Try

<?php
    // version 1.02
    // display errors for debugging
    ini_set("display_errors", true);
    error_reporting(E_ALL);

    // Open a connection to the SQL server so we can run queries later. 
    $conn = new mysqli(removed for privacy); // DON'T FORGET TO EDIT THIS PART!
    // Output error info if there was a connection problem
    if ($conn->connect_errno) {
        die("<h3> Uh oh! It looks like we're having trouble connecting to the 
 website at the moment. Try again soon! {$conn->connect_error}</h3>");
    }

    // file name
    $file_contents = $_FILES["uploadedfile"]["tmp_name"];

    $SQL_statement = "LOAD DATA LOCAL INFILE '$file_contents' INTO TABLE C_CAMPER FIELDS TERMINATED BY ','
                        LINES TERMINATED BY '\n' IGNORE 3 LINES
                        (camperFName,campLName,camperAddress,camperCity,camperZip,camperCountry,camperPhone,camperEmail,@camperAltEmail,@camperDOB,camperAge,camperSchool,camperGradYear,camperGPA,`camperACT/SAT`,camperPar,camperParPhone,camperPrimPos,camperSecPos)
                        SET camperDOB = STR_TO_DATE(@camperDOB, '%m/%d/%Y'),
                            camperAltEmail = NULLIF(@camperAltEmail, 'null');";
    // Run SQL query
    if( !$conn->query($SQL_statement)) {
        echo $conn->error;
    }

    // Close the SQL connection
    $conn->close();
?>

edited Apr 09 '17 at 20:09

answered Apr 09 '17 at 19:20

Vasiliy Zverev

622
5
10

What do you mean is it initialized? I will post the whole PHP page for you to see, with your changes made. Edit: I can't get it to post the code in this reply, so I will edit it into the original post. Thanks! – Jacobra Apr 09 '17 at 19:31
@Jacobra, I mean you have to connect to mysql server and select database beforehand. Also updated STR_TO_DATE() just now. – Vasiliy Zverev Apr 09 '17 at 19:34
It is still not working. It's not even doing any error reporting for the PHP portion since adding that part. – Jacobra Apr 09 '17 at 19:52
@Jacobra, please try this new version. – Vasiliy Zverev Apr 09 '17 at 19:54
I did, and it is still just saying "this page isn't working" when I click submit. Do you think there is something wrong with the HTML portion of the code? – Jacobra Apr 09 '17 at 19:58
@Jacobra, I added version numbers ;) please try 1.01. There was missing semicolon in previous version, I also removed session_start() because you don't use it in this file. – Vasiliy Zverev Apr 09 '17 at 20:03
Okay, I tried that, and I got this error: "Fatal error: Call to undefined method mysqli::error() in file location(changed for privacy) on line 26" – Jacobra Apr 09 '17 at 20:08
@Jacobra, fixed. v.1.02. – Vasiliy Zverev Apr 09 '17 at 20:09
Now it says "The used command is not allowed with this MariaDB version"... haha is this impossible? – Jacobra Apr 09 '17 at 20:11
@Jacobra, it seems you should enable it: http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile – Vasiliy Zverev Apr 09 '17 at 20:13
@Jacobra, if your MariaDB is on localhost, you can remove `LOCAL` from SQL and retry. – Vasiliy Zverev Apr 09 '17 at 20:16
@Jacobra, if you can't change security settings of your MariaDB and it is not on localhost, you can parse csv in php using fgetcsv() and run `INSERT ... VALUES (..., ..., ...)` query. – Vasiliy Zverev Apr 09 '17 at 20:19
I removed the LOCAL and it said "Access denied for user 'removed'@'localhost' (using password: YES)" I might need to just use the fgetcsv(). I had read this was an easier way to do it which is why I was trying to do it this way, but maybe it just won't work. I am using phpMyAdmin. With the fgetcsv, how do I handle the date being in the wrong format? – Jacobra Apr 09 '17 at 20:27
@Jacobra, if you need it just one time, you can import your csv using phpMyAdmin. – Vasiliy Zverev Apr 09 '17 at 20:32
I don't need it just one time, it needs to be able to be used repeatedly. – Jacobra Apr 09 '17 at 20:35
@Jacobra, do you have "localhost" in `$conn = new mysqli(removed for privacy)`? – Vasiliy Zverev Apr 09 '17 at 20:43
Yes, I do. What does the local mean? Maybe I am just getting things wrong right from the start. All of the files are hosted on cpanel... but this CSV is not going to be coming from those files. When the user goes to upload this CSV file, it is just coming from their computer's files. – Jacobra Apr 09 '17 at 20:47
@Jacobra, `LOCAL` in `LOAD DATA` means that csv is not on MySQL server, but it is on web (php) server. If you connect mysqli to localhost, it means that MySQL server and web server share the same file directories (though they might be restricted by file permissions). Let's test that your MySQL user has access to your db. Run `connect_errno) die("Connection: {$conn->connect_error}");` `if( $conn->query("SELECT 1 FROM C_CAMPER"))` `echo "Success"; else echo $conn->error;` – Vasiliy Zverev Apr 09 '17 at 20:59
I am pretty sure it has access, I have done a lot of other queries to the database using PHP. – Jacobra Apr 09 '17 at 21:03
@Jacobra, does C_CAMPER table already exist? If not, is your MySQL user has CREATE TABLE permissions? – Vasiliy Zverev Apr 09 '17 at 21:05
Yes, the table already exists. It does not have any data in it yet, though. – Jacobra Apr 09 '17 at 21:09
@Jacobra, as suggested here: http://stackoverflow.com/a/16765364/1435647 let's try in phpMyAdmin: `GRANT FILE on database.* to user@'localhost' IDENTIFIED BY 'password'` Replace database, user and password by actual values. – Vasiliy Zverev Apr 09 '17 at 21:12
Where in phpMyAdmin should I try this? I input it in the SQL part and it said " #1045 - Access denied for user 'removed'@'localhost' (using password: YES)" – Jacobra Apr 09 '17 at 21:20
@Jacobra, then you have to use fgetcsv() approach. You don't have enough rights to use LOAD DATA INFILE. – Vasiliy Zverev Apr 09 '17 at 21:22
Is there anywhere on cpanel or phpMyAdmin where I can change the rights? – Jacobra Apr 09 '17 at 21:24
@Jacobra, or maybe try to grant FILE permission for MariaDB user through cPanel. – Vasiliy Zverev Apr 09 '17 at 21:25
I can't figure it out. I guess I will just have to try to do it with fgetcsv() approach... Very discouraging.. Thanks for all of your help!!! You have been awesome. I'll probably be back on here at some point needing help with the fgetcsv() knowing me... – Jacobra Apr 09 '17 at 21:31
@Jacobra, https://documentation.cpanel.net/display/ALD/MySQL+Databases#MySQLDatabases-CurrentDatabases "To modify a user's privileges for a specific database..." – Vasiliy Zverev Apr 09 '17 at 21:36
I did that, it didn't do anything.. still just says the command won't work. The two users it lists are different then the one that is in the connection, though... they are username_remote and username_webpage where username is the user that is used in the connection within the PHP file. – Jacobra Apr 09 '17 at 21:50

Uploading CSV file into mysql database using PHP

1 Answers1