mysql load data infile through php script - not working

Question

I am trying to use the mysql LOAD DATA LOCAL INFILE to get some csv data into my mysql database through a php script using mysqli. This is what my sql string looks like:

LOAD DATA LOCAL INFILE '/var/www/html/dashmaker/uploads/HHdata.csv' INTO TABLE dashmaker.HHdata FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

This is what my php script looks like:

$sql = "LOAD DATA LOCAL INFILE '/var/www/html/dashmaker/uploads/HHdata.csv'
       INTO TABLE dashmaker.HHdata
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '\"' 
       LINES TERMINATED BY '\n' 
       IGNORE 1 LINES;";

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

$result = mysqli_query($sql, $con);

if (mysql_affected_rows() == 1) {
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysql_error(); 
};

echo $message;

mysqli_close($con);

I found that I needed to set the mysql my.cnf to include local-infile under [mysql] and [mysqld] - so I have done that.

When I run the sql query through the shell it works. When I try to do it through the php script the error message ($message) I now get says:

The user update failed: Access denied for user ''@'localhost' (using password: NO)

One weird thing is that it doesn't show any user name before the @'localhost'. Can't understand why. Besides this, I use the same connection setting to run regular SELECT queries from the database using php scripts. The user also has FILE privileges.

I have searched extensively but haven't found anything that can explain what's going on. Any advice would be much appreciated.

You're mixing MySQL APIs with `mysql_` and `mysqli_` functions. They do **not** mix; use `mysqli_` exclusively. Plus, you're not using brackets in `[user]` etc, are you? This isn't MSSQL, it's MySQL, remove them. — Funk Forty Niner, Oct 08 '14 at 06:00
Plus, in `mysqli_`, DB connection comes first, invert these `$result = mysqli_query($sql, $con);` to read as `$result = mysqli_query($con, $sql);` — Funk Forty Niner, Oct 08 '14 at 06:03
Thank you! Incredibly quick response! It is working. I think the error message was also throwing me off - mysql_affected_rows() is now mysql_affected_rows($con)... and I wasn't using square brackets for user/password - just meant to indicate these were placeholders. Thanks again! — Andre Ling, Oct 08 '14 at 06:17

Funk Forty Niner · Accepted Answer · 2014-10-08T06:19:55.457

You're mixing MySQL APIs with mysql_ and mysqli_ functions in a few instances.

mysql_affected_rows()
mysql_error()

They do not mix together; use mysqli_ exclusively.

Plus, you're not using brackets in [user] etc, are you? That is MSSQL syntax, remove them.

Plus, in mysqli_, DB connection comes first, invert these $result = mysqli_query($sql, $con); to read as $result = mysqli_query($con, $sql);

$sql = "LOAD DATA LOCAL INFILE '/var/www/html/dashmaker/uploads/HHdata.csv'
       INTO TABLE dashmaker.HHdata
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '\"' 
       LINES TERMINATED BY '\n' 
       IGNORE 1 LINES;";

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

$result = mysqli_query($con, $sql);

if (mysqli_affected_rows($con) == 1) {
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysqli_error($con); 
};

echo $message;
mysqli_close($con);

mysql load data infile through php script - not working

1 Answers1