Search code examples
phpmysqlsqlload-data-infile

mysql_connect with --local-infile parameter

Can not load data from uploaded (local) file since upgrade of mysql (current version: Server version: 5.5.44-0+deb8u1 (Debian)), files implied are:

dbconnection.php

<?php
$server = "localhost";
$user = "TheUser";
$pass = "ThePass";
$db_name = "DbName";
$link = mysql_connect($server, $user, $pass);
mysql_select_db($db_name);
mysql_set_charset('utf8', $link);
?>

send2db.php

<?php
include 'dbconnection.php';
mysql_select_db("DbName") or die(mysql_error());
$query = "LOAD DATA LOCAL INFILE '$file' INTO TABLE `T1` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' ";
mysql_query($query) or die(mysql_error());
?>

The error says:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Inside mysql:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

But if I access mysql this way, files can be loaded:

mysql --local-infile -p

So my question is, can I set this option in the dbconnection.php file, I've tried many ways already with no success, I've been reading posts about my.cnf configuration and some other stuffs but nothing works for me, any suggestion?

Thanks

UPDATE: I've been away changing the code of the entire web to mysqli, ufff!!, well following the suggestions from the answers bellow I did the next code but no success, I still get the message: "The used command is not allowed with this MySQL version". Implied files are next:

acessdb.php

<?php
$link = new mysqli($server, $user, $pass, $dbname);
?>

send2db.php

<?php include 'acessdb.php';
$link->options(MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_query($link, "LOAD DATA LOCAL INFILE 'upfiles/file.csv' INTO TABLE `T1` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"'") or die(mysqli_error($link));
$link->options(MYSQLI_OPT_LOCAL_INFILE, false);
?>

Any suggestions?

Solution

  • Ok, finally I found the way, here is the working code:

    file: connectdb.php

    $link = mysqli_init();
mysqli_options($link, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($link, $server, $user, $pass, $dbname);

    file: send2db.php

    mysqli_query($link, "LOAD DATA LOCAL INFILE 'upfiles/file.csv' INTO TABLE `T1` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"'") or die(mysqli_error($link));

    I hope it helps.