Insert data into mysql from CSV file in php

Question

I am working on application in which i need to upload data through a csv file and then save record into mysql table. I am doing this in php. Till now i have right this code.

 <?php
 //date_default_timezone_set("Asia/Karachi");
//echo "The time is " . date("Y-m-d h:i:sa");
$DateTime=date("Y-m-d h:i:sa");
 $FilePath=$_POST['img'];
 $ID=$_POST['ID'];
 $Query_String2="";
// echo $FilePath;
 $row = 1;
 if(isset($FilePath))
 {
    // echo "ID is =".$ID;
       $Query_String1 = "INSERT into ap_form_$ID VALUES";

        if (($handle = fopen($FilePath, "r")) !== FALSE)
         {
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
            {
                if($row!=1)
                $Query_String2=$Query_String2.",";
                $Query_String2= $Query_String2."("."NULL".","."'".$DateTime."'".","."NULL".","."'127.0.0.1'".","."1".","."NULL";
                $num = count($data);
                $row++;
                $master = array();
                for ($c=0; $c < $num; $c++) 
                {

                    if($c==0)
                        $Query_String2=$Query_String2.","."'".$data[$c]."'";
                    else
                        $Query_String2=$Query_String2.","."'".$data[$c]."'";
                //$master[$c]= $data[$c];
                 }
                       $Query_String2=$Query_String2.")";


                  }

                  $Final_Query=$Query_String1.$Query_String2;
                  echo "Final Query =".$Final_Query;
                $connection = mysqli_connect("localhost", "root","","form_db");

                $result = mysqli_query($connection,$Final_Query) or mysql_error();

                if($result > 0)
                {
                    echo "
                    <script type=\"text/javascript\">
                     alert('Record has been inserted into the form');
                    </script>
                ";
                }
                        else
                {
                        echo "
                    <script type=\"text/javascript\">
                     alert('I am sorry there is some problem');
                    </script>
                ";

                }
    fclose($handle);

What i am doing in this code is, i read data from CSV file and then make a long query string like "Insert into table value ("bla, bla ,bal),("bla, bla, bla)" This code work fine for the small data just like when i have data upto 1000 in the csv file But when data is more the 1000 , the process stuck and i am not be able to save data in mysql.In my application i need to upload 50000 at minimum. Any idea or solution to solve this problem.

Answer 1

If you have the CSV file on the same server where you run MySQL you could also use LOAD DATA INFILE to load the CSV file directly into database, e.g.

LOAD DATA INFILE 'yourfile.csv' 
 INTO TABLE yourtable
 FIELDS TERMINATED BY ',';

See MySQL Reference Manual for LOAD DATA INFILE for more details.

EDIT:

You may enable LOAD DATA LOCAL to allow loading CSV files from the web server into the database but there are security considerations to be taken into account. Again, reference manual more has details:

There are two potential security issues with supporting the LOCAL version of LOAD DATA statements:

• The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access.
• In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any command against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server.

To enable this on server side use:

SET GLOBAL local_infile=ON;

After which you can verify this:

> SHOW GLOBAL VARIABLES like "%local%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

You also need to enable this in the php.ini:

[MySQL]
; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
; http://php.net/mysql.allow_local_infile
mysql.allow_local_infile = On

---

Once you have LOAD DATA LOCAL enabled, you may use load CSV files from web server directly into the database:

LOAD DATA LOCAL INFILE 'yourfile.csv' 
 INTO TABLE yourtable
 FIELDS TERMINATED BY ',';

If you need to modify the data while you load it, you can first store it in local variables and modify it while inserting into database, e.g:

LOAD DATA LOCAL INFILE 'yourfile.csv' 
 INTO TABLE yourtable
 FIELDS TERMINATED BY ','
 (@c1,@c2)
 SET column1=NULL, column2=SUBSTRING(@c2,2,3), column3='127.0.0.1';