load data infile doesn't execute in php

Question

I am encountering a problem with load data function of mysql, this is that it gives me an error whenever I try to execute the code as follows. Any help with this would be humbled and appreciated (: Thank you

Before Debugging:

<?php
ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL);
ini_set('max_execution_time',0);
ini_set('upload_max_filesize', '50M');
ini_set('post_max_size', '50M');
ini_set('max_input_time', 0);
ini_set("memory_limit", "-1");
set_time_limit(0);
$ip = getenv('REMOTE_ADDR');
$conn=mysqli_connect("somehost","someusr","somepw", "somedb") or die("Could not connect");



if(isset($_POST["submit_file"]))
{
 $op = $_POST['op'];
 $month = $_POST['month'];
 $year = $_POST['year'];
 chmod($file["tmp_name"], 0444);
 $file = $_FILES["file"]["tmp_name"];
 $file_open = fopen($file,"r");
 while(($csv = fgetcsv($file_open,1000, ",")) !== false)
 {
  $ct = $csv[0];
  $ts = $csv[1];
  $cd = $csv[2];
  $pc = $csv[3];
  $lt = $csv[4];
  $nt = $csv[5];
  $xp = $csv[6];
  $fn = $csv[7];
  $sn = $csv[8];
  $ds = $csv[9];
  $pp = $csv[10];
  $sql="
      load data local infile '$file' replace into table anew fields terminated by ',' lines terminated by '\n' (`ct`, `ts`, `cd`, `pc`, `lt`, `nt`, `xp`, `fn`, `sn`, `ds`, `pp`, `uploadedby`);
";
  $result=mysqli_query($conn, $sql);
  if($result){
      echo "<script type=\"text/javascript\">alert(\"success\");window.location.href = '/insights/datauploader/';</script>";    
  }
  else{
      echo "<script type=\"text/javascript\">alert(\"failure" .  $mysqli->error . "\");window.location.href = '/insights/datauploader/';</script>"; 
      
  }
  

 }
}
?>

After Debugging:

<?php
ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL);
ini_set('max_execution_time',0);
ini_set('upload_max_filesize', '50M');
ini_set('post_max_size', '50M');
ini_set('max_input_time', 0);
ini_set("memory_limit", "-1");
set_time_limit(0);
$ip = getenv('REMOTE_ADDR');
$server="somehost";
$user="someuser";
$pass="somepw";
$dbname="somedb";
$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($conn, $server, $user, $pass, $dbname);


if(isset($_POST["submit_file"]))
{
 $op = $_POST['op'];
 $month = $_POST['month'];
 $year = $_POST['year'];
 $file = $_FILES["file"]["tmp_name"];
 $file_open = fopen($file,"r");
  $sql="
      load data local infile '$file' replace into table anew fields terminated by ',' lines terminated by '\n'  (`ct`, `ts`, `cd`, `pc`, `lt`, `nt`, `xp`, `fn`, `sn`, `ds`, `pp`) ;
"   ;
  $result=mysqli_query($conn, $sql);
  if($result){
      echo "<script type=\"text/javascript\">alert(\"success.\");window.location.href = '/insights/datauploader/';</script>";   
  }
  else{
        echo("Error description: " . mysqli_error($conn));
      
  }
  

 
}
?>

New error is :

Can't find file 'C:WindowsTempphpB367.tmp'.

did you mean `$pp` instead of `pp` in your SQL query? Try not to just echo ` — Raptor, Aug 05 '21 at 09:28
*it gives me an error whenever I try to execute the code* Do you want us to predict what is the error message and what line of the code produces it? — Akina, Aug 05 '21 at 09:30
@Raptor I guess I had a mistake with specifying columns , I have fixed that by removing uploadedby from $sql but still not executing — Ryham Ali Maher Abouelnour, Aug 05 '21 at 09:33
@ADyson I did this by echoing sqli error in the failed part but it shows nothing. Is there any other way to get the exact error? — Ryham Ali Maher Abouelnour, Aug 05 '21 at 09:34
Yes. https://stackoverflow.com/a/22662582/5947043 explains how to set up error handling properly in mysqli. I believe I already sent you this for a previous question. — ADyson, Aug 05 '21 at 09:37
So I did this by removing the redirection script . In action file page it gives me a number or errors , first one is that file variable is not defined, second one that server tries to access array offset on value of type null , third that chmod() has no such file or directory in folder encolsing my action file — Ryham Ali Maher Abouelnour, Aug 05 '21 at 09:44
I removed the chmod() line , no other errors that come out from php — Ryham Ali Maher Abouelnour, Aug 05 '21 at 09:47
Sorry, it took me some time to get this sorted. Yes error from mysql says that loading local data is disabled, now I'll be fixing this from mysql server — Ryham Ali Maher Abouelnour, Aug 05 '21 at 09:50
this was from this line in php.ini ;mysqli.allow_local_infile = On , it was commented that was why php has prohibited executing the load data function — Ryham Ali Maher Abouelnour, Aug 05 '21 at 09:53
@ADyson now I have enabled data load from both php and mysqli servers. New error I am getting is that file to be uploaded is not found, path to the file misses slashes as in mysqli error like so > Error description: Can't find file 'C:WindowsTempphpDB9C.tmp'. — Ryham Ali Maher Abouelnour, Aug 05 '21 at 10:10
Hmm, why are you still looping over the CSV file? Load data will import the whole file at once, you don't need to loop over each line — ADyson, Aug 05 '21 at 10:23
@ADyson well yes, I've removed it as yes no longer need it. But still mysqli error is that it can't find the temp file to be uploaded and it shows the path to the temp file having slashes truncated from path and that it can't find the tmp file , obviously because there is something that causes the slashes to be truncated may be. — Ryham Ali Maher Abouelnour, Aug 05 '21 at 10:50
I have added how it looks my new code after all the debugging as above in my question, do you know what could be possible reason that slashes get truncated from the path to temp file? — Ryham Ali Maher Abouelnour, Aug 05 '21 at 10:58
Ok. Have you tried saving the file to a non-temp folder (using move_uploaded_file()) and telling mysql to load it from there. (You can then delete it once the process is complete, obviously). — ADyson, Aug 05 '21 at 11:18

Siva Koteswara Rao · Answer 1 · 2021-08-05T11:07:08.437

-1

You can only load the from database server local path. You can make this as a shared folder. You can copy the files located in the network to this common folder before loading into the database

I think you have to use // in your file path

$file = $_FILES["file"]["\tmp_name"];

edited Aug 05 '21 at 11:07

answered Aug 05 '21 at 11:01

Siva Koteswara Rao

129
1
6

`You can only load the from database server local path`...not if `LOCAL` is used, which it has been. – ADyson Aug 05 '21 at 11:15
We can use LOCAL keyword – Siva Koteswara Rao Aug 05 '21 at 11:26
Yes that's what I just said. But your answer claims it is not possible. – ADyson Aug 05 '21 at 11:27
@SivaKoteswaraRao tried but it gives even more errors to handle – Ryham Ali Maher Abouelnour Aug 05 '21 at 11:56
Can u try / instead of back slash(\) LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test – Siva Koteswara Rao Aug 05 '21 at 12:28
One thing is all users may not have access rights on c://Windows. you can use c://windows/temp or some other path. Can try hard coding the file name with path instead of $file – Siva Koteswara Rao Aug 05 '21 at 12:34

load data infile doesn't execute in php

1 Answers1