Martin AJ Martin AJ - 4 months ago 8
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

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