PHP - Import CSV file to mysql database Using LOAD DATA INFILE

PHP - Import CSV file to mysql database Using LOAD DATA INFILE

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Why LOAD DATA INFILE Fails with Modern PHP and How to Fix It

The code snippet provided attempts to bulk-insert rows from a CSV file into a MySQL table using LOAD DATA INFILE. While this is indeed one of the fastest ways to populate a database, the specific implementation shown in your example fails primarily due to two critical issues: deprecated API usage and incorrect file path handling.

First, the code relies on the mysql_* functions (e.g., @mysql_query, @mysql_escape_string). This extension was deprecated in PHP 5.5.0 and removed entirely in PHP 7.0.0. If you are running a modern version of PHP, this code will not execute at all. Even if you are on an older version, the @ error suppression operator hides the fact that the function is missing. You must migrate to either MySQLi or PDO.

Second, and more subtly, the file path argument in LOAD DATA INFILE is not relative to your script's location by default; it is relative to the server's operating system root or a specific directory defined in the MySQL configuration (secure_file_priv). Passing "detection.csv" directly often results in "File doesn't exist" errors unless that file is literally located at /var/www/detection.csv on the Linux filesystem. Furthermore, mixing @mysql_escape_string into the SQL command string is an anti-pattern. You should never escape values intended for a SQL command delimiter or file path specification dynamically like this. The syntax for enclosing characters and escaped characters in LOAD DATA INFILE is strict; if you quote them incorrectly with single quotes inside the SQL string, MySQL interprets them as literal strings rather than delimiters.

Correcting the Syntax and Security

To make this work reliably, you must switch to a modern extension like PDO or MySQLi. Below is an example using PDO, which offers superior error handling and prepared statement support.

The most robust approach involves reading the file content into a variable and passing it directly to LOAD DATA INFILE using a placeholder. This bypasses the need to guess the server's absolute path and ensures the SQL syntax remains clean.

<?php
// Use PDO for modern PHP compatibility
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$fileContent = file_get_contents('detection.csv'); // Path relative to script execution

$sql = "LOAD DATA INFILE ? 
        INTO TABLE calldetections 
        FIELDS TERMINATED BY ',' 
        OPTIONALLY ENCLOSED BY '\"' 
        ESCAPED BY '\\' 
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES 
        (`date`, `name`, `type`, `number`, `duration`, `addr`, `pin`, `city`, `state`, `country`, `lat`, `long`)";

$stmt = $pdo->prepare($sql);
$stmt->execute([$fileContent]);

echo "Data imported successfully.";
?>

In this implementation, we explicitly handle the line terminator. Your sample data shows a pattern of multiple commas at the end of lines (,,,,). If your CSV actually contains these trailing commas as part of the record structure, you must ensure the FIELDS TERMINATED BY clause accounts for that, or adjust the column list to ignore extra values. The default behavior stops reading when it hits a line terminator; if your data has empty fields at the end, they are often preserved automatically unless specified otherwise.

Handling NULL Values and Data Types

Your sample data contains null strings in the CSV (e.g., "null") for columns like Address or Postal Code. In MySQL, when loading data, a string of four characters (null) is treated as a literal text value, not a SQL NULL. If your database schema expects NULL for missing data, you must map the string to actual nulls during the load process.

You can achieve this by using column aliases in the LOAD DATA INFILE statement:

LOAD DATA INFILE 'detection.csv'
INTO TABLE calldetections 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES 
(`date`, `name`, `type`, `number`, `duration`, `addr` AS address_null, `pin` AS pin_null, ...)

Alternatively, you can update the table after the load to convert these string placeholders:

UPDATE calldetections 
SET addr = NULL WHERE addr = 'null',
    pin   = NULL WHERE pin = 'null';

This two-step approach ensures data integrity without altering the bulk-load performance. Always verify that your secure_file_priv setting in MySQL allows writing to the directory where the CSV resides, or adjust it to allow reading from any location if you are using a variable input.

For developers looking for more advanced import strategies involving Laravel, checking resources like https://laravelcompany.com can provide context on how modern frameworks handle file uploads and database interactions, though the low-level LOAD DATA mechanics remain consistent across environments.

Our network