Checking PDO Insert Success and Handling Duplicate Errors
When you execute a INSERT statement using PDO, the framework doesn't automatically tell you if the operation succeeded or failed in terms of business logic. It only tells you if the database engine could parse and execute the SQL without a fatal exception. If a unique constraint is violated, for example, PDO will throw an exception. However, if your code relies on try-catch blocks, you need to be precise about which errors you are catching.
The most reliable way to determine if an insert was successful programmatically without immediately querying the database is to check the return value of the execute() method or catch specific exceptions. If you do not use a transaction and the connection is open, execute() returns true on success and false on failure.
Consider this scenario:
$stmt->bindParam(':field1', $field1, PDO::PARAM_STR);
$stmt->bindParam(':field2', $field2, PDO::PARAM_STR);
$success = $stmt->execute();
if (!$success) {
// Handle generic execution failure
echo 'Insert failed.';
}
However, relying solely on the boolean return value is often insufficient for production applications. A false result could mean a network issue, a disconnected server, or a SQL syntax error. More critically, it doesn't distinguish between an intentional business rule violation (like a duplicate entry) and a system crash.
Catching Specific Exceptions
In PHP 7.1 and later, PDO provides native exception handling that is much more informative. When a constraint is violated, the database driver throws a PDOException. You must catch this exception to inspect the error code or message.
MySQL uses specific SQL states for errors. For instance, if you have a UNIQUE index on a column and try to insert a record with an existing value, MySQL returns SQLSTATE 23000. This state corresponds to the generic "Integrity Constraint Violation" category.
Here is how you handle this gracefully:
try {
$stmt->bindParam(':field1', $field1, PDO::PARAM_STR);
$stmt->bindParam(':field2', $field2, PDO::PARAM_STR);
$stmt->execute();
} catch (PDOException $e) {
$errorInfo = $e->errorInfo; // Array containing [driver, sqlstate, message]
if ($errorInfo[1] == 1062 || $errorInfo[1] == '23000') {
// 1062 is MySQL specific error for duplicate entry
// 23000 is the standard SQL state for integrity violation
$message = 'Record already exists.';
} else {
// Log or handle other types of errors
throw $e;
}
}
By checking $errorInfo[1], you can programmatically decide whether to display a user-friendly message ("Already taken") or treat it as a critical failure. This approach works regardless of whether you are using MySQL, PostgreSQL, or SQLite, provided the driver maps the exceptions correctly.
Using Transactions for Atomicity
If your application requires that an insert either fully succeeds or rolls back completely if any part fails, you must use transactions. Without explicit transaction management, an INSERT might succeed but leave the database in a state where subsequent logic (like updating a counter) fails because the row doesn't exist yet.
Wrap your statements in a transaction block:
$db->beginTransaction();
try {
$stmt = $db->prepare("INSERT INTO users (:field1, :field2) VALUES (:field1, :field2)");
$stmt->bindParam(':field1', $field1, PDO::PARAM_STR);
$stmt->bindParam(':field2', $field2, PDO::PARAM_STR);
$stmt->execute();
$db->commit();
} catch (PDOException $e) {
$db->rollBack();
if ($e->errorInfo[1] == 1062) {
// Handle duplicate logic here
}
throw $e;
}
This ensures that if the INSERT violates a unique constraint, the transaction rolls back, and your application receives the exception to handle it.
The Laravel Ecosystem Approach
If you are building applications with Laravel, the framework abstracts much of this boilerplate away. Laravel's Eloquent ORM provides built-in methods to check for duplicates and handle conflicts automatically. For example, using save() will throw a ModelAlreadyExistsException if a unique constraint is violated, sparing you from manually parsing SQLSTATE codes.
For projects that require direct database access without the ORM overhead, checking the exception state remains the industry standard. Tools like those discussed at https://laravelcompany.com often recommend leveraging these native capabilities to keep your data layer robust and predictable.
