How can I know if insert .. select did actually insert any rows?

Question

Here is my code:

$stm = $dbh
->prepare("INSERT INTO resend_pass(user_id, token, date_time)
            SELECT ?, ?, unix_timestamp()
            FROM dual
            WHERE NOT EXISTS( SELECT count(*) AS num_week,
                                     COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1  day))),0)  as num_day,
                                     COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1  hour))),0) as num_hour,
                                     COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 minute))),0) as num_1min
                                  FROM resend_pass
                                 WHERE user_id   = ?  
                                   AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))
                                HAVING num_week > 11 OR num_day > 5 OR num_hour > 3 OR num_1min > 0 );");

if($stm->execute(array(10, 'token', 10))){
    echo 'inserted';
} else {
    echo 'failed';
}

My script always prints inserted, even when no row inserted. Why? And how can I fix it?

Noted that I've used this approach to know if it inserted successfully.

BeetleJuice · Answer 1 · 2016-07-13T03:00:20.567

1

execute returns false only if there was an error. Your script echoes 'inserted' because there was no error. In short, your query was run successfully. Nothing was inserted because no record met the conditions in your SQL's WHERE clause

If you need to know whether a row was actually inserted, call the rowCount() function instead:

if($stm->execute(...)===false):
    //there was an error
elseif($stm->rowCount()<1): // $stm->affected_rows if you use MySQLi
    //no row was inserted
else:
    //at least one row was inserted
endif

Documentation here.

edited Jul 13 '16 at 03:00

answered Jul 13 '16 at 02:53

BeetleJuice

39,516
19
105
165

PDO throws exceptions, therefore checking the execute() result is quite pointless – Your Common Sense Jul 13 '16 at 03:22
@YourCommonSense I didn't know this; help me with a reference please. The docs on PDOStatement::execute say that "FALSE is returned on failure" and make no mention of exceptions (http://php.net/manual/en/pdostatement.execute.php) – BeetleJuice Jul 13 '16 at 03:30
you can simply google for 'PDO exceptions' I believe. – Your Common Sense Jul 13 '16 at 03:33

score 0 · Answer 2 · answered Jul 13 '16 at 02:56

0

How about using the function mysqli_affected_rows() http://php.net/manual/es/mysqli.affected-rows.php

If you are using PDO (I think is your case) you may want to try $stm->rowCount().

Prepared statements will return the number of affected rows via that method.

answered Jul 13 '16 at 02:56

sandino

3,813
1
19
24

1

Yeah I guess `rowCount()` is what I'm looking for. thank you upvote. – Martin AJ Jul 13 '16 at 03:00

score -1 · Accepted Answer · edited Jul 13 '16 at 03:53

-1

INSERT ... SELECT is considered successful when it INSERTs all the rows returned by SELECT; if the SELECT returns zero rows then the statement will successfully insert zero rows.

To determine if rows have been inserted, one has to test the number of affected rows; with PDO this is done by querying the `PDOStatement::rowCount() method, something like this:

try {
    $stm = $dbh->prepare("INSERT .. SELECT query");
    $stm->execute();
    if ( $stm->rowCount() > 0 ) {
        echo 'inserted';
    } else {
        throw new Exception('No rows inserted');
    }
} catch(Exception $e) { 
    echo $e;
}

edited Jul 13 '16 at 03:53

Martin AJ

6,261
8
53
111

answered Jul 13 '16 at 03:00

Darwin von Corax

5,201
3
17
28

Great +1 .. Just can you please rewrite your code and use `try/catch` instead of `if` statement? Like this `if ($stm->rowCount() > 0) throw;`. Also *"No rows found"* and *"failed"* are identical for me. – Martin AJ Jul 13 '16 at 03:05
Don't you know that PDO can throw exceptions by itself? – Your Common Sense Jul 13 '16 at 03:11
Also I think I can write `$stm->execute(array(10, 'token', 10))` out of that `if` statement, right? – Martin AJ Jul 13 '16 at 03:12
@YourCommonSense Not when *"no row inserted"*. PDO only throws an exception there is an error. – Martin AJ Jul 13 '16 at 03:12
@YourCommonSense This is an `INSERT ... SELECT`, not a regular `INSERT`. It's possible to successfully insert zero rows. – Darwin von Corax Jul 13 '16 at 03:16
Yes I see now. I am thinking how to edit the title and your answer. – Your Common Sense Jul 13 '16 at 03:17
I'd be careful with this. It does not allow you to distinguish between `no row inserted` and `DB error`. The latter may need the admin's attention, or they just may require different handling, so I think it's best to separate them as I did. – BeetleJuice Jul 13 '16 at 03:18
@DarwinvonCorax Why you don't write `$stm->execute(array(10, 'token', 10))` out of that condition? – Martin AJ Jul 13 '16 at 03:20
@BeetleJuice That's a god point. As written, my code *would* eat a DB error, wouldn't it? – Darwin von Corax Jul 13 '16 at 03:23
@BeetleJuice in fact it does. But it's better to get execute from condition anyway, as it should never return you false, and therefore makes this code misleading. – Your Common Sense Jul 13 '16 at 03:25
@MartinAJ My code is really just meant as a throwaway example. I would expect you do make any alterations or optimizations you see necessary to suit your application. – Darwin von Corax Jul 13 '16 at 03:29
Current code has this error: `Fatal error: Call to a member function rowCount() on boolean in {path}` – Martin AJ Jul 13 '16 at 03:44
@MartinAJ Notice `INSERT ... SELECT` will only succeed if all selected rows could be inserted. In case there was any duplicate key, all insertions fail. If you don't want this behavior can also use `INSERT IGNORE ... SELECT` instead, that will insert any record it can and ignore any attempt of inserting duplicate keys. – Havenard Jul 13 '16 at 03:58

How can I know if insert .. select did actually insert any rows?

3 Answers3