What are the best practices for handling updates, inserts, and deletions in a MySQL database when syncing with a .csv file in a PHP script?

When syncing a MySQL database with a .csv file in a PHP script, it is important to handle updates, inserts, and deletions efficiently to ensure data integrity. One approach is to first read the .csv file and compare its contents with the database records. For updates, you can use the ON DUPLICATE KEY UPDATE clause in MySQL to update existing records or insert new ones. For inserts, you can use INSERT INTO table_name (columns) VALUES (values) to add new records. For deletions, you can compare the .csv file with the database records and delete any records that are no longer present in the .csv file. 

```php
<?php
// Connect to MySQL database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

// Read .csv file
$csv_file = 'data.csv';
$csv_data = array_map('str_getcsv', file($csv_file));

// Loop through .csv data
foreach($csv_data as $row) {
    $id = $row[0];
    $name = $row[1];
    
    // Check if record exists in database
    $sql = "SELECT * FROM table_name WHERE id = $id";
    $result = $conn->query($sql);
    
    if($result->num_rows > 0) {
        // Update existing record
        $sql = "UPDATE table_name SET name = '$name' WHERE id = $id";
        $conn->query($sql);
    } else {
        // Insert new record
        $sql = "INSERT INTO table_name (id, name) VALUES ($id, '$name')";
        $conn->query($sql);
    }
}

// Delete records not present in .csv file
$sql = "SELECT id FROM table_name";
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
    $id = $row['id'];
    
    $found = false;
    foreach($csv_data as $csv_row) {
        if($csv_row[0] == $id) {
            $found = true;
            break;
        }
    }
    
    if(!$found) {
        // Delete record
        $sql = "DELETE FROM table_name WHERE id = $id";
        $conn->query($sql);
    }
}

$conn->

Keywords

MySQL CSV file PHP script updates deletions

Related Questions