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

Question

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?

Wouldn't `affected_rows` be 0 if nothing had been inserted? Although if nothing had been inserted it would presumably be because `execute` had failed. Have you tried `if ($stmt->affected_rows < 1)`? — Michael, Apr 03 '12 at 11:02

score 108 · Accepted Answer · edited Jul 22 '22 at 15:20

108

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.

edited Jul 22 '22 at 15:20

Your Common Sense

156,878
40
214
345

answered Apr 03 '12 at 11:02

Manse

37,765
10
83
108

2

i took a flier on using this method. i googled to make sure, and it led me to this answer. upvoted so more people will use this in their scripts. – r3wt Mar 30 '14 at 17:09
10

This does not ensure that an insert ever happened! Only that there was not an error. – user1032531 Feb 09 '16 at 20:11
1

@user1032531: You are correct. An `INSERT ... SELECT` could insert zero rows and return successfully. In the case of OP query... `INSERT INTO .. VALUES` ... I'm not aware of any scenario where the statement would complete successfully without inserting a row. (It's possible I've overlooked some possibility.) – spencer7593 Apr 10 '17 at 01:30
The problem with this is that an UPDATE would return true even if it had updated zero records. – João Jun 11 '19 at 19:43
Is there any case where this would not work? I am doing the if statement above. I can echo before the execute but I get no echo in either parts of the if statement. It just dies. This is when I am inserting when the id already exists. Do I really need to test if the id exists before doing an insert with MySQLi? – Dan Zen Nov 13 '20 at 03:18
@DanZen it maybe that you have configured MySQLi to throw exceptions - if you have then you need to surround the execute statement with a try/catch statement - you can then look at the error in the Exception – Manse Nov 17 '20 at 08:36
1

@manse - thanks for the response. I found the answer https://stackoverflow.com/a/41077076/2255555 - the STRICT_TRANS_TABLES was turned on by the server folks - which meant all SQL fields have to have a default value. Got the server folks to turn that off and all is good. – Dan Zen Nov 28 '20 at 05:12

score 34 · Answer 2 · answered Apr 03 '12 at 11:08

34

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.

answered Apr 03 '12 at 11:08

MattP

2,798
2
31
42

Álvaro González · Answer 3 · 2022-07-22T16:06:49.383

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.

I upvoted because of the edit. Every mysqli function can fail, and checking for their return value is not feasible or smart. Enabling mysqli error reporting is the correct way. — Dharman, Nov 09 '19 at 20:03

score 7 · Answer 4 · answered Mar 24 '19 at 07:33

7

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());
}

answered Mar 24 '19 at 07:33

CBEK

127
1
5

score 5 · Answer 5 · edited Apr 03 '12 at 11:17

5

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();
}

edited Apr 03 '12 at 11:17

Manse

37,765
10
83
108

answered Apr 03 '12 at 11:11

mishu

5,347
1
21
39

score -2 · Answer 6 · answered Aug 20 '17 at 15:49

-2

Other way:

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

answered Aug 20 '17 at 15:49

Pedro Antônio

395
1
6
19

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

6 Answers6

Linked

Related