Martin AJ Martin AJ - 1 year ago 40
MySQL Question

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

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 Source

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:

if ($stm->execute(array(10, 'token', 10)) && ($stm->rowCount() > 0))
{
    echo 'inserted';
}
else
{
    throw new Exception('No rows inserted');
}