How can the primary key relationships be maintained when updating data from a CSV file to a MySQL database using PHP?

When updating data from a CSV file to a MySQL database using PHP, it is important to ensure that the primary key relationships are maintained. One way to do this is to check if a record with the same primary key already exists in the database before inserting or updating the data. If a record with the same primary key exists, the data should be updated instead of inserted. This can be achieved by using the ON DUPLICATE KEY UPDATE statement in the SQL query. 

<?php
// Connect to MySQL database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Loop through CSV data and update database
foreach($csvData as $data) {
    $id = $data[0];
    $name = $data[1];
    $email = $data[2];
    
    $sql = "INSERT INTO table_name (id, name, email) VALUES ('$id', '$name', '$email') 
            ON DUPLICATE KEY UPDATE name='$name', email='$email'";
    
    $result = $conn->query($sql);
}

// Close database connection
$conn->close();
?>

Keywords

PHP MySQL primary key CSV file data update

Related Questions