如何知道insert .. select是否确实插入了任何行？

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.

Answer 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;
}