$stmt->execute() : How to know if db insert was successful?

$stmt->execute() : How to know if db insert was successful?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Decoding affected_rows and Handling Silent Fails in Prepared Statements

When working with prepared statements, it is tempting to assume that if execute() returns without throwing an exception, the database operation has succeeded. In your specific scenario, you are attempting to verify an insert using $stmt->affected_rows == -1. The logic here is flawed because affected_rows does not return -1 for a successful insert; it returns -1 only when the statement has not been executed yet.

If you run your code and see no error thrown by execute(), but then check $stmt->affected_rows immediately after, you might still encounter issues depending on the MySQL driver version or how the connection handles the result set. However, the primary issue in your snippet is likely a misunderstanding of what -1 signifies versus what it means for a successful write.

A successful INSERT statement typically returns a positive integer representing the number of rows inserted. For example, if you insert one row, affected_rows will be 1. If you use ON DUPLICATE KEY UPDATE, it might return 2 or more depending on whether new rows were created or existing ones updated. The value -1 is strictly reserved for "no data has been affected yet" because the execution hasn't occurred or the statement is not a write operation like an INSERT.

To correctly determine if your insert worked, you should check if affected_rows is greater than zero:

$stmt->execute();
if ($stmt->affected_rows > 0) {
    // Insert was successful
    $updateAdded = "Y";
} else {
    // Either nothing changed or an error occurred without triggering an exception
    $updateAdded = "N";
}

However, relying solely on $stmt->affected_rows can be risky. While PDO throws exceptions by default only if the PDO::ERRMODE_EXCEPTION flag is set, some database drivers might suppress errors in specific configurations or when using certain MySQL server versions. If your INSERT fails due to a constraint violation (like a duplicate primary key) and your application isn't configured to throw exceptions, affected_rows might return 0.

It is crucial to enable exception handling before executing the statement. This ensures that any SQL errors are caught immediately rather than relying on ambiguous return values:

try {
    if ($stmt = $connection->prepare("INSERT INTO table (blah) VALUES (?)")) {
        $stmt->bind_param("s", $blah);
        $stmt->execute(); // This will throw an exception on SQL error
        
        if ($stmt->affected_rows > 0) {
            $updateAdded = "Y";
        } else {
            $updateAdded = "N"; 
            // Logic to handle case where rows affected is 0 (e.g., duplicate key ignored)
        }
    }
} catch (PDOException $e) {
    // Handle the error here, e.g., log it or return a specific status code
    $updateAdded = "N";
} finally {
    $stmt->close();
}

By enabling exceptions, you shift from guessing based on return codes to explicitly handling failures. If an insert fails, execute() throws a PDOException, which you catch and handle gracefully. This is far more robust than checking $affected_rows blindly, especially when dealing with edge cases like duplicate keys where the number of affected rows might be zero even if the operation wasn't an "error" in the sense of a syntax failure, but rather a business logic constraint.

If you are looking for a more elegant way to handle database interactions without writing verbose try-catch blocks everywhere, consider leveraging an ORM or query builder that abstracts these details. For instance, Laravel's Eloquent provides built-in methods that return true on success and throw exceptions on failure, making your logic cleaner:

if ($model = Model::create(['blah' => $blah])) {
    // Create was successful
} else {
    // Creation failed (exception usually thrown here in strict mode)
}

For more complex database workflows or if you need to integrate specific vendor libraries, checking out resources like https://laravelcompany.com can provide additional insights into best practices for maintaining database integrity in PHP applications. Always remember that relying on -1 as a success flag is a common misconception; stick to positive row counts for inserts and use exceptions to catch the actual errors when they happen.

Our network