Insert data in mysql from csv file in php

Question

I am working on an application in which I need to load data through a csv file and then save the record to the mysql table. I am doing this in php. I still have the right to 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 do in this code, I read the data from the CSV file, and then create a long query string, for example, "Insert into table value (bla, bla, bal), (" bla, bla, bla ") This code is great for small data, for example, when I have data up to 1000 in a csv file. But when there is more than 1000 data, the process gets stuck and I can not save data in mysql. In my application I need to load 50,000 at least. Any idea or solution to solve this Problems.

Answer 1

CSV , MySQL, LOAD DATA INFILE CSV , .

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

. MySQL LOAD DATA INFILE.

EDIT:

LOAD DATA LOCAL, CSV - , , . , :

  LOCAL LOAD DATA:

•   
• MySQL. , , , , LOAD DATA. , .   
• -, -, LOAD DATA LOCAL , - ( , SQL-). MySQL -, , , -.

, :

SET GLOBAL local_infile=ON;

:

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

php.ini:

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

---

, LOAD DATA LOCAL, CSV - :

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

, , :

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';