0

In a Joomla 3 component I use the MySQL "LOAD DATA LOCAL INFILE" command to import large CSV files into a Joomla database table.

When moving to a new server, it is no longer possible to configure MySQL via php.ini like this:

mysqli.allow_local_infile = On

The web host suggests to use this PHP command:

mysqli_options($mysqli, MYSQLI_OPT_LOCAL_INFILE, true);

What I would like to achieve is to be able to do this with the Joomla database connection object.

A) Adding a $options parameter to the JConfig object in Joomla's configuration.php did not work:

public $options = array('local_infile' => true); // <-- ??

B) Alternatively, I tried creating another database connection like this:

 $option['driver'] = 'mysqli'; // Database driver name $option['host'] = 'host'; // Database host name $option['user'] = 'user'; // User for database authentication $option['password'] = 'pass'; // Password for database authentication $option['database'] = 'name'; // Database name $option['prefix'] = 'xxx_'; // Database prefix (may be empty) $option['option'] = array('local_infile' => true); // <-- ??? $db = JDatabaseDriver::getInstance($option);

Experimentation with replacing local_infile with allow_local_infile, mysqli.local_infile, or MYSQLI_OPT_LOCAL_INFILE does not seem to work either.

Will this even be possible with Joomla? Is there any other best practice for importing large CSV files into a Joomla database?

Improve this question

1 Answer 1

Reset to default
1

Updated to include the more detailed example based on the 1st comment.

I have been able to get the follow statement to work, or at least not fail, in a simple Joomla 3 Model;

$db = $this->getDbo(); if ($db->getconnection() instanceof mysqli) { mysqli_options($db->getConnection(), MYSQLI_OPT_LOCAL_INFILE, true); }

However I haven't look too hard into how to confirm that it has made the required change in the MySQL connection so you will need to do that with your function.

Improve this answer
6
  • I have tried to convert this to Joomla 3 like this - but this did not work either: $connection = $db->getConnection(); if ($connection instanceof JDatabaseMySQLi) { $mysqliConnection = $connection->getDriver()->getConnection(); mysqli_options($mysqliConnection, MYSQLI_OPT_LOCAL_INFILE, true); } Commented Jun 30, 2023 at 14:13
  • 1
    I have updated the answer to show a working example as per your comment above. Commented Jul 1, 2023 at 8:12
  • FYI: In one of my queries, it seems to be working and is loading the CSV data correctly, but is still throwing the error. - Currently I do not see any side effects. Commented Jul 1, 2023 at 17:27
  • On Joomla 4 the driver seems to load correctly, but I get this error message if I run my LOAD DATA LOCAL INFILE ... SQL with this code in an try catch block: '$db->setQuery($sql); $result = $db->execute();' This is the error message: This command is not supported in the prepared statement protocol yet Any ideas? Commented Jul 28, 2023 at 19:39
  • You might like to open a new question with the message you are now getting and include what is held in $sql. You can refer/link back to this question for context. Commented Jul 29, 2023 at 8:10

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.