What are some best practices for importing data from a CSV file into an SQL table using PHP?

When importing data from a CSV file into an SQL table using PHP, it's important to properly handle the data and ensure that it is formatted correctly before inserting it into the database. One common approach is to use the fgetcsv() function to read the CSV file line by line and then insert the data into the SQL table using prepared statements to prevent SQL injection attacks. 

<?php
// Establish a connection to the database
$pdo = new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');

// Open the CSV file for reading
$csvFile = fopen('data.csv', 'r');

// Loop through each line of the CSV file
while (($data = fgetcsv($csvFile)) !== false) {
    // Prepare a SQL statement to insert the data into the table
    $stmt = $pdo->prepare("INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)");
    
    // Bind the data from the CSV file to the SQL statement parameters
    $stmt->bindParam(1, $data[0]);
    $stmt->bindParam(2, $data[1]);
    $stmt->bindParam(3, $data[2]);
    
    // Execute the SQL statement
    $stmt->execute();
}

// Close the CSV file
fclose($csvFile);
?>

Keywords

CSV file SQL table import PHP functions error handling

Related Questions