0

I'm attempting to upload a zip file of CSVs (exported from my DB) into mysql tables using LOAD DATA INFILE

Mysql seems to be getting the wrong path:

For example, the following:

C:\xampp\htdocs\site/uploads/temp/1620203716052941000/ck_address_change.csv

returns this error: C:\xampp\mysql\data\xampphtdocssite\uploads\temp\1620203716052941000\ck_address_change.csv not found (Errcode: 2 "No such file or directory")

//Get file and create directory
$file = $_FILES['import_zip']['tmp_name'];
$rand = preg_replace('/(0)\.(\d+) (\d+)/', '$3$1$2', microtime()); //random folder name as will be deleted later
mkdir(dirname(__DIR__).'/uploads/temp/'.$rand, 0777);
$path = dirname(__DIR__).'/uploads/temp/'.$rand;

//Open Zip
$zip = new ZipArchive;
$res = $zip->open($file);
if($res === TRUE) {
    $zip->extractTo($path);
    $zip->close();  
} 

//Loop files
$files = array_diff(scandir($path), array('.', '..'));  
foreach($files as $file){
    $table_name = rtrim($file,'.csv');
    $upload = $path.'/'.$file;
    $q = "LOAD DATA INFILE '".$upload."' INTO TABLE $table_name";
    $results = mysqli_query($db,$q) or die(mysqli_error($db));
    if(!$results) {
        printf("Error message: %s\n", mysqli_error($db));     
    }   
}

//Remove Directory
array_map('unlink', glob("$path/*.*"));
rmdir($path);

How do I set the path properly for this? Or is this an issue on my local server?

Note this is just for testing use on my local server so no security issues etc.

charliechina
  • 123
  • 9
  • Seems pretty easy to deduce from that message that mysql expects you to save the data somewhere under `C:\xampp\mysql\data\ ` and then provide a _relative_ path to the LOAD DATA command – ADyson May 05 '21 at 09:11
  • Alternatively you can specify an absolute path - which I see you have attempted to do. But you need to format that path according to the rules of what the command understands. When using any command which you are unfamiliar with, I would always strongly suggest [reading the manual](https://dev.mysql.com/doc/refman/8.0/en/load-data.html) before you start coding. In this case, pay particular attention to the "Input File Character Set" section. Come back if you still have a problem once you've read the instructions. – ADyson May 05 '21 at 09:13
  • It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman May 05 '21 at 10:18
  • @Dharman - thanks but as I stated, this is just for testing so security issues are not an issue – charliechina May 05 '21 at 14:20
  • Security issues aside, it's still not correct code. Enable MySQLi error reporting properly – Dharman May 05 '21 at 14:21
  • Thanks @ADyson, that put me on the right track. Backslashes instead of forward slashes in the file path – charliechina May 05 '21 at 14:41
  • Great. I would echo Dharman's comment though - just because it's test code doesn't mean you shouldn't observe good practices about error handling and security. Set yourself into good habits and then you won't have to remember how to do it "properly" when it comes to "real" code - it'll just be instinctive. – ADyson May 05 '21 at 14:49

1 Answers1

0

Working code - replaced forward slashes with backslashes in the path.

//Get file and create directory
$file = $_FILES['import_zip']['tmp_name'];
$rand = preg_replace('/(0)\.(\d+) (\d+)/', '$3$1$2', microtime()); //random folder name as will be deleted later
mkdir(dirname(__DIR__).'/files/temp/'.$rand, 0777);
$path = dirname(__DIR__).'/files/temp/'.$rand;

//Open Zip
$zip = new ZipArchive;
$res = $zip->open($file);
if($res === TRUE) {
    $zip->extractTo($path);
    $zip->close();  
} 

//Loop files
$files = array_diff(scandir($path), array('.', '..'));  
foreach($files as $file){
    $table_name = rtrim($file,'.csv');
    $upload = $path.'\\'.$file;
    $upload = str_replace('/','\\', $upload);
    $upload = str_replace('\\', '\\\\', $upload);
    $q = "LOAD DATA INFILE '$upload' INTO TABLE $table_name";
    $results = mysqli_query($db,$q) or die(mysqli_error($db));
    if(!$results) {
        printf("Error message: %s\n", mysqli_error($db));     
    }   

}

//Remove Directory
array_map('unlink', glob("$path/*.*"));
rmdir($path);
charliechina
  • 123
  • 9