CODEWITHSUNDEEP
phpmysqlsqlpdo
dotancohen
dotancohen

Reputation: 31481

How to know if new row was inserted, or if extant row was updated?

When using ON DUPLICATE KEY UPDATE in MySQL (with PDO), how can one check if an extant row was updated, or in a new row was inserted? I'm using PHP's PDO with a prepared query to insert:

$sql = "INSERT INTO some_table (f_name, l_name) ";
$sql.= "VALUES (:f_name, :l_name) ";
$sql.= "ON DUPLICATE KEY UPDATE ";
$sql.= "    f_name = VALUES(f_name) ";
$sql.= "    l_name = VALUES(l_name) ";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':f_name', 'Jeff');
$stmt->bindValue(':l_name', 'Atwood');
$stmt->execute();

Now I would like to output Updated! or Added! as appropriate. How can I check this?

Upvotes: 2

Views: 212

Answers (1)

Daksh Mehta
Daksh Mehta

Reputation: 124

You can check by checking number of affected rows.

Whenever a row is inserted it returns exactly same number as number of inserted rows. However, when it update, It multiply by 2 and return as affected rows.

For example,

If you are adding two row, and Row is create: affected row would be 2 Row is updated: affected row would be 4

Reference: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Also, to check number of affected rows you can use rowCount function ( https://www.php.net/manual/en/pdostatement.rowcount.php )

Upvotes: 3

Related Questions