LOAD DATA INFILE Method for MYSQL in PHP

Question

I want to import data stored in a .csv file into mysql via php. The LOAD DATA INFILE query does not seem to work and i managed to write a code that will import the records one by one. Here is the code:

<?php

$arr = array(array(),array());
$num = 0;
$row = 0;
$handle = fopen("./import.csv", "r");


while($data = fgetcsv($handle,1000,",")){   
    $num = count($data);
    for ($c=0; $c < $num; $c++) {
            $arr[$row][$c] = $data[$c];
    }
    $row++;
}


$con = mysql_connect('localhost','root','password22');
mysql_select_db("security",$con);

for($i=1; $i<$row; $i++){
$sql = "INSERT INTO sls VALUES ('".$arr[$i][0]."','".$arr[$i][1]."','".$arr[$i][2]."','".$arr[$i][3]."','".$arr[$i][4]."','".$arr[$i][5]."')";
mysql_query($sql,$con);
}

?>

The problem is that I have about 300 000 records to import and when the records get too much, no record gets imported into the database and I get an error message. Is there anyway I can import the data faster or are there any similar statements like LOAD DATA INFILE I can use in PHP?

Your problem is probably the timeout. I would recommend you to split the file and the read one by one. As this answer that I give for another person. Here is the link: stackoverflow.com/questions/19738694/php-parsing-csv-with-ftell/… — Jorge Campos
– Jorge Campos, Commented Nov 24, 2013 at 17:16
you have not shared the code you tried with LOAD DATA INFILE — sakhunzai
– sakhunzai, Commented Dec 31, 2013 at 3:43

AWA · Accepted Answer · 2013-12-31 03:37:31Z

This may help you out

<?php
require_once 'reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('filename.xls');

$con=mysqli_connect("localhost","username","password","dbname");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Create table
$sql="CREATE TABLE tablename(

    quote_number VARCHAR(100),
    line_no VARCHAR(100),
    item_no VARCHAR(100),
    name VARCHAR(100),
    unit VARCHAR(100),
    rm VARCHAR(100),
    capex VARCHAR(100))";

// Execute query
if (mysqli_query($con,$sql))
  {
  echo "Table tablename created successfully";
  }
else
  {
  echo "Error creating table: " . mysqli_error($con);
  }

for($x = 2; $x <= count($data->sheets[0]["cells"]); $x++)
 {
    //$sno = $data->sheets[0]["cells"][$x][1];
    $quote_number = $data->sheets[0]["cells"][$x][1];
    $line_no = $data->sheets[0]["cells"][$x][2];
    $item_no = $data->sheets[0]["cells"][$x][3];
    $name = $data->sheets[0]["cells"][$x][4];
    $unit = $data->sheets[0]["cells"][$x][5];
    $rm = $data->sheets[0]["cells"][$x][6];
    $capex = $data->sheets[0]["cells"][$x][7];

    $res = mysqli_query($con,"INSERT INTO tablename
    (quote_number, line_no, item_no,name,unit,rm,capex) VALUES ('$quote_number','$line_no','$item_no','$name','$unit','$rm','$capex')");
mysqli_close($res);
}
?>

you can download reader.php file from here

sakhunzai · Accepted Answer · 2013-12-31 04:04:09Z

Since you have not shared the LOAD DATA INFILE I assume most probably the issue is with FILE Privileges

You have to GRANT FILE privileges to the person loading the file(Mysql user connecting through username/password from php). Sth like this:

 GRANT FILE on *.* TO 'mysql_user'@'localhost' ;

Since FILE is global privilege you cannot localize that to a specific database e.g dbname.* , just to note. To run the above command itself you should login to mysql from command line as root or as a user who has GRANT privileges.

Once that is done you should able to load the file using LOAD DATA INFILE. IF you are trying this in shared-hosting environment, your chances are very little. In that case you have to use the above method you tried , read the file, split the each line ,validate the line , insert into db table.

Collectives™ on Stack Overflow

LOAD DATA INFILE Method for MYSQL in PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest