PHP MySQLi mysqli_set_local_infile_handler()

mysqli_set_local_infile_handler()

The mysqli_set_local_infile_handler() function allows us to set a callback function for processing LOAD DATA LOCAL INFILE requests. This is crucial for securely transferring data from a local text file directly into a MySQL table.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);

if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// Callback function that will process the data stream in real-time
function my_loader($fp, &$buffer, $buflen, &$err) {
    $content = fread($fp, $buflen);
    // NOTE: Here is where the data processing magic happens.
    // We convert every chunk of text read from the file to lowercase (strtolower).
    // The database will receive the pre-formatted text.
    $buffer .= strtolower($content); 
    return strlen($content);
}

// Set our custom function to intercept the import
mysqli_set_local_infile_handler($connection, "my_loader");

// Execute the command for lightning-fast import from the local file fruits.txt
mysqli_query($connection, "LOAD DATA LOCAL INFILE 'fruits.txt' INTO TABLE local_file_table");

echo "Local infile handler has been successfully configured.";

// Always reset the handler back to default behavior after the import is finished
mysqli_set_local_infile_default($connection); 
mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Allows defining custom logic for reading files.

Increases security, as we control which parts of the file are actually loaded.

An ideal tool for quickly populating the database with external data (e.g., CSV import).