Uploading and storing CSV into mysql db using PHP

Question

I'm trying to create a file upload form in which te user can simply upload a CSV and then it's parsed and values are uploaded in a mysql database. I have already managed to get a lot of my code working. Here's what I have so far:

HTML of upload form:

<form method="post"
        enctype="multipart/form-data">
<table>
    <tr>
        <td>
            Filename:
        </td>
        <td>
            <input type="file" name="file" id="file">
        </td>
    </tr>
    <tr>
        <td colspan="2" align="right">
            <input type="submit" name="submit" value="Submit">
        </td>
    </tr>
</table>
</form>

PHP code I have so far:

<?php
if(isset($_POST['submit'])) {
    if ($_FILES["file"]["error"] > 0)
    {
        echo "Error: " . $_FILES["file"]["error"] . "<br>";
    }
    else
    {
        echo "Upload: " . $_FILES["file"]["name"] . "<br>";
        echo "Type: " . $_FILES["file"]["type"] . "<br>";
        echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br>";
        $a=$_FILES["file"]["tmp_name"];
    }   

    $csv_file = $a; 

    if (($getfile = fopen($csv_file, "r")) !== FALSE) {
       while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {

                $result = $data;
                $str = implode(";", $result);
                $slice = explode(";", $str);

                $col1 = $slice[0];
                $col2 = $slice[1];
                $col3 = $slice[2];
                $col4 = $slice[3];

                $query = "INSERT INTO persons(id, name, email ,contacts) VALUES('".$col1."','".$col2."','".$col3."','".$col4."')";
                mysql_query($query);
                echo $query;

        }
    }
    echo "File data successfully imported to database!!";
}
?>

Now the problem is when I'm reading the file and try to create an array of it I don't get a multidimensional array so that I can query row by row. When I look into the $slice array it looks like this:

Array
(
    [0] => 1
    [1] => Jack
    [2] => jack@hotmail.com
    [3] => Bart
2
    [4] => Bart
    [5] => bart@hotmail.com
    [6] => John
3
    [7] => John
    [8] => john@hotmail.com
    [9] => Jack
)

When I echo out the query that I get with this script I get:

INSERT INTO persons(id, name, email ,contacts) VALUES('1','Jack','jack@hotmail.com','Bart 2')

How can I fix this so I get a db row for every row in my csv? I hope I gave enough information to solve my question.

You should look into `load data infile` with MySQL rather than this approach. Much faster and does most of the work for you. — Fluffeh, Nov 12 '14 at 09:16
http://stackoverflow.com/questions/11077801/import-csv-to-mysql-table can help you — Shri, Nov 12 '14 at 09:20
That doesn't seem to be the problem Naruto. But thanks for noticing! I will look into that way to do this Fluffeh, thanks for the suggestion. — , Nov 12 '14 at 09:20
I tried your suggestions @Fluffeh and @Shri but my query doesn't seem to work. What is wrong with this query?: '`$query = "LOAD DATA LOCAL INFILE '".$_FILES["file"]["tmp_name"]."' INTO TABLE persons FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES (name, email, contacts)"` — , Nov 12 '14 at 09:36
That also doesn't seem to work. But when I echo out the query the value for LINES TERMINATED BY disappears. What could be the cause of this? — , Nov 12 '14 at 09:52

score 0 · Answer 1 · answered Nov 12 '14 at 09:34

Try this,

if (($getfile = fopen($csv_file, "r")) !== FALSE) {
     while($data = ! feof($getfile ))   {           

            $query = "INSERT INTO persons(id, name, email ,contacts) VALUES('".fgetcsv($getfile[0] )."','".fgetcsv($getfile[1])."','".fgetcsv($getfile[2] )."','".fgetcsv($getfile[3] )."')";
            mysql_query($query);
            echo $query;

    }
}

Uploading and storing CSV into mysql db using PHP

1 Answers1