Martin AJ Martin AJ - 1 year ago 90
MySQL Question

How can I know if insert was successful?

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
, 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';
    throw new Exception('No rows inserted');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download