What are the best practices for reading data from a CSV file and inserting it into a MySQL database using PHP?

When reading data from a CSV file and inserting it into a MySQL database using PHP, it is important to handle errors, sanitize input data to prevent SQL injection, and use prepared statements for database queries to enhance security. Additionally, it is recommended to open and close the database connection only once to improve performance. 

<?php

// Establish a connection to the MySQL database
$mysqli = new mysqli("localhost", "username", "password", "database_name");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Read data from the CSV file
$csvFile = fopen('data.csv', 'r');
while (($data = fgetcsv($csvFile)) !== false) {
    // Sanitize input data
    $data = array_map(array($mysqli, 'real_escape_string'), $data);
    
    // Insert data into MySQL database using prepared statements
    $stmt = $mysqli->prepare("INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $data[0], $data[1], $data[2]);
    $stmt->execute();
}

// Close the CSV file and database connection
fclose($csvFile);
$mysqli->close();

?>

Keywords

CSV file MySQL database PHP data insertion reading data

Related Questions