CODEWITHSUNDEEP
phpmysqli
cosmicsafari
cosmicsafari

Reputation: 4049

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

With the following piece of code, how do i know that anything was inserted in to the db?

if ($stmt = $connection->prepare("insert into table (blah) values (?)")) {
$stmt->bind_param("s", $blah);  
$stmt->execute();           
$stmt->close();                                 
}

I had thought adding the following line would have worked but apparently not.

if($stmt->affected_rows==-1){$updateAdded="N"; echo "failed";}

And then use the $updatedAdded="N" to then skip other pieces of code further down the page that are dependent on the above insert being successful.

Any ideas?

Upvotes: 57

Views: 110808

Answers (6)

Álvaro González
Álvaro González

Reputation: 146370

Starting on PHP/8.1.0, the default setting is to throw exceptions on error, so you don't need to do anything special. Your global exception handler will take care of it, or you can try/catch for specific handling.

For older versions, you can check the manual pages of whatever function you are using:

prepare() - returns a statement object or FALSE if an error occurred.
bind_param() - Returns TRUE on success or FALSE on failure.
execute() - Returns TRUE on success or FALSE on failure.
close() - Returns TRUE on success or FALSE on failure.

In practice, though, this gets annoying and it's error prone. It's better to configure mysqli to throw exceptions on error and get rid of all specific error handling except for the few occasions where an error is expected (e.g., a tentative insert that might violate a unique constraint):

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Default value used to be MYSQLI_REPORT_OFF. On PHP/8.1.0 it changed to MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT.

Upvotes: 13

Manse
Manse

Reputation: 38147

The execute() method returns a boolean ... so just do this :

if ($stmt->execute()) { 
   // it worked
} else {
   // it didn't
}

Update: since 2022 and beyond, a failed query will throw an error Exception. So you won't have to write any code to "skip other pieces of code further down the page" - it will be skipped automatically. Therefore you shouldn't add any conditions and just write the code right away:

$stmt = $connection->prepare("insert into table (blah) values (?)");
$stmt->bind_param("s", $blah);  
$stmt->execute();

If you need to do something in case of success, then just do it right away, like

echo "success";

You will see it only if the query was successful. Otherwise it will be the error message.

Upvotes: 110

CBEK
CBEK

Reputation: 127

You can check the returned value after the execute :

if ($stmt->execute()) { 
    // ok :-)
    $count = $stmt->rowCount();
    echo count . ' rows updated properly!';
} else {
    // KO :-(
    print_r($stmt->errorInfo());
}

Upvotes: 7

Pedro Antônio
Pedro Antônio

Reputation: 405

Other way:

if ($stmt->error){
        echo "Error";
    }
    else{
        echo "Ok";
    }

Upvotes: -2

mishu
mishu

Reputation: 5397

if you mean that you want to know the number of affected rows you can use rowCount on the pdo statement

$stmt->rowCount();

after execute;

if you are talking about error handling I think the best option is to set the errmode to throwing exteptions and wrap everything in a try/catch block

try
{
    //----
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

Upvotes: 5

MattP
MattP

Reputation: 2863

Check the return value of $stmt->execute()

if(!$stmt->execute()) echo $stmt->error;

Note that line of code does perform the execute() command so use it in place of your current $stmt->execute() not after it.

Upvotes: 36

Related Questions