What are some common pitfalls to avoid when using PHP to differentiate between updating existing data and inserting new data into a database?

One common pitfall is not properly checking if the data already exists in the database before attempting to insert new data. To avoid this, you can use a SELECT query to check if the data exists before deciding whether to perform an INSERT or UPDATE operation. 

// Check if data already exists in the database
$query = "SELECT * FROM table_name WHERE column_name = :value";
$stmt = $pdo->prepare($query);
$stmt->execute(['value' => $value]);

if($stmt->rowCount() > 0) {
    // Perform an UPDATE operation
    $query = "UPDATE table_name SET column_name = :new_value WHERE column_name = :value";
} else {
    // Perform an INSERT operation
    $query = "INSERT INTO table_name (column_name) VALUES (:value)";
}

$stmt = $pdo->prepare($query);
$stmt->execute(['value' => $value]);

Keywords

SQL injection PDO prepared statements duplicate keys error handling

Related Questions