What are some best practices for maintaining data integrity when importing CSV files into a database using PHP?

When importing CSV files into a database using PHP, it is important to ensure data integrity by validating and sanitizing the data before inserting it into the database. This can be done by checking for any missing or incorrect data, filtering out unwanted characters, and using prepared statements to prevent SQL injection attacks. 

// Read the CSV file
$csv = array_map('str_getcsv', file('data.csv'));

// Connect to the database
$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', 'username', 'password');

// Prepare a SQL statement
$stmt = $pdo->prepare("INSERT INTO mytable (column1, column2, column3) VALUES (:value1, :value2, :value3)");

// Loop through each row in the CSV file
foreach($csv as $row) {
    // Validate and sanitize the data
    $value1 = filter_var($row[0], FILTER_SANITIZE_STRING);
    $value2 = filter_var($row[1], FILTER_SANITIZE_STRING);
    $value3 = filter_var($row[2], FILTER_SANITIZE_STRING);

    // Bind the values to the prepared statement
    $stmt->bindParam(':value1', $value1);
    $stmt->bindParam(':value2', $value2);
    $stmt->bindParam(':value3', $value3);

    // Execute the statement
    $stmt->execute();
}

Keywords

CSV files database data integrity PHP functions error handling

Related Questions