What best practices should be followed when importing CSV data into a MySQL database using PHP?

When importing CSV data into a MySQL database using PHP, it is important to follow best practices to ensure data integrity and security. One common approach is to use prepared statements to prevent SQL injection attacks and to properly handle errors during the import process. Additionally, it is recommended to validate and sanitize the data before inserting it into the database to avoid any potential issues. 

<?php

// Establish a connection to the MySQL database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

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

// Open and read the CSV file
$csvFile = 'data.csv';
$csv = array_map('str_getcsv', file($csvFile));

// Prepare a SQL statement for inserting data into the database
$stmt = $conn->prepare("INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)");

// Bind parameters and insert data row by row
foreach ($csv as $row) {
    $stmt->bind_param("sss", $row[0], $row[1], $row[2]);
    $stmt->execute();
}

// Close the statement and connection
$stmt->close();
$conn->close();

?>

Keywords

CSV MySQL database import PHP

Related Questions