Parameterizing file name in MYSQL LOAD DATA INFILE

Question

Hi

Is there a way to dynamically specify a file name in the LOAD DATA INFILE? Can it be parameterized like for instance (syntax may be incorrect) LOAD DATA INFILE '$filename'?

Answer 1

A citation from MySQL documentation:

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

That means that it can not be a parameter of a prepared statement. But no one forbids to make the string interpolation while the statement is just a string in your PHP code.

Answer 2

If you're asking if it can be used in a script; you can do some thing like this with php:

<?php
$mysqli = new mysqli("host", "user", "pwd", "db");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}


$sql = "CREATE TABLE number1 (id INT PRIMARY KEY auto_increment,data TEXT)";
if ($result = $mysqli->query($sql)) {
} else {
printf("<br>%s",$mysqli->error);

}

$host  = $_SERVER['HTTP_HOST'];
$uri   = rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
$filename = "data.csv";

$sql = "LOAD DATA LOCAL INFILE '$host$uri$filename' INTO TABLE number1";
if ($result = $mysqli->query($sql)) {

} else {
printf("<br>%s",$mysqli->error);    
}
// Close the DB connection
$mysqli->close();

exit;
%>

If the file is in the same folder as the script just use $filename a instead of $host$uri$filename. I put this together quick from a couple scripts I'm using, sorry if it doesn't work without debug, but it should be pretty close. It requires mysqli.