Importing CSV data using PHP/MySQL

Importing CSV data using PHP/MySQL

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

Stefan Bogdanescu

Founder & Senior Architect

Importing CSV data using PHP/MySQL

Importing raw CSV files into a database is a common requirement, but the implementation details often trip up developers who rely on outdated examples. The code snippet provided in your source material relies on the deprecated mysql_* functions and uses a procedural approach that, while functional for small datasets, introduces significant security vulnerabilities and performance bottlenecks.

The most critical issue in the example is the use of TRUNCATE TABLE. This command removes all existing records without firing any triggers or allowing transactions to roll back if an error occurs later in the script. If you need to preserve data integrity or audit logs, you should use a DELETE statement inside a transaction or simply verify that your table has no foreign key constraints before clearing it.

Handling Data Types and Display Issues

You mentioned that text fields are displaying as 0. This is almost certainly a client-side rendering issue rather than a database corruption problem. If your database column is defined as VARCHAR or TEXT, the data should be stored exactly as provided in the CSV. However, if the column type is mistakenly set to INT or another numeric type, PHP's HTML output might try to interpret non-numeric strings as numbers, resulting in 0.

Ensure your database schema matches your data:

  • Text columns must be VARCHAR, TEXT, or CHAR.
  • Number columns should be INT or DECIMAL. If a text field contains "123" and you query it without quotes in SQL, some drivers might cast it to an integer. Always use prepared statements (which we will discuss next) to prevent implicit type casting issues.

Escaping Quotes and Special Characters

Regarding data enclosed in quotes: CSV files often wrap fields in double quotes (") if they contain commas or newlines. For example, a field might look like "Smith, John". If you simply concatenate these values into an SQL query, the quotes can break the statement syntax.

The fgetcsv() function handles the parsing logic for you, but it returns the raw string from the file. You must sanitize this data before inserting it. In the legacy code example, using backticks (`) around column names or escaping single quotes within values is essential. A safer approach involves building the query with explicit escaping:

// Escape single quotes and handle newlines to prevent SQL injection
$text = str_replace("'", "''", $data[0]); 
$number = intval($data[1]); // Ensure it's treated as an integer

$sql = "INSERT INTO importing (text, number) VALUES ('$text', $number)";
mysql_query($sql) or die(mysql_error());

Skipping Header Rows

To ignore the first few lines of a CSV (headers), you can use a simple counter or the fgetcsv() return value. The function returns FALSE when it hits the end of the file, but you can also check if the row contains expected data types. A robust pattern looks like this:

$handle = fopen($_FILES['filename']['tmp_name'], "r");

// Skip the first 1 line (header)
fgetcsv($handle, 1000, ","); 

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    // Your INSERT logic here
}
fclose($handle);

Data Integrity and Execution Time

You asked if data format changes during the process. Generally, VARCHAR columns store text exactly as inserted, preserving decimals only if you define them in a specific column type like DECIMAL(10,2). However, standard FLOAT or INT types may truncate precision or remove decimal points depending on your SQL mode (e.g., STRICT_TRANS_TABLES).

The "Maximum execution time exceeded" error you encountered after uploading 10,000 records is a classic performance issue with row-by-row insertion. Executing an INSERT statement for every single line in the CSV forces the database to write to disk and manage transactions individually for each record. This creates a high I/O overhead.

For large datasets, batch processing is mandatory. Instead of looping through every line, you can accumulate rows into a single multi-value insert query or use the LOAD DATA INFILE command directly from the server side if the file path is accessible. Alternatively, consider using Laravel's Eloquent ORM or Query Builder with batch operations, which are significantly faster and more secure than raw SQL concatenation.

If you are maintaining legacy PHP code, migrating to Laravel provides a robust framework for handling file uploads, queue jobs for processing large CSVs, and safe database interactions without reinventing the wheel.

Our network