CODEWITHSUNDEEP
phpmysql
Michael Samuel
Michael Samuel

Reputation: 3920

PHP check if MYSQL query was an insert or update

I have a simple MYSQL query:

INSERT INTO table (col1,col2) VALUES ('1','2') 
ON DUPLICATE KEY UPDATE col1 = '1', col2 = '2'

I use PHP PDO statements to query the database. Is there a way to know if the query executed resulted into a new inserted row or an existing was updated?

Upvotes: 2

Views: 2603

Answers (3)

Power Engineering
Power Engineering

Reputation: 722

Just use mysqli_affected_rows,it returns the number of rows affected by the last INSERT, UPDATE, REPLACE or DELETE query.

From PHP documentation:

In the case of "INSERT ... ON DUPLICATE KEY UPDATE" queries, the return value will be 1 if an insert was performed, or 2 for an update of an existing row.

see https://www.php.net/manual/en/function.mysql-affected-rows.php

From Mysql manual:

"With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated."

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

This is the most reliable way to do it.

Upvotes: 1

Paul
Paul

Reputation: 136

maybe you put the answer right into the query like:

INSERT INTO table (col1,col2, col_type) VALUES ('1','2','inserted') ON DUPLICATE KEY UPDATE col1 = '1', col2 = '2', col_type = 'updated'

Upvotes: 0

Ali
Ali

Reputation: 3461

One way to do so is to get the number of rows before executing the query, then get the number of rows after executing the query, if they're not equal, it means a new row was inserted and if they are equal, it means a row was updated.

$sql = "SHOW TABLE STATUS LIKE 'TABLE_NAME'";
$stmt = $pdo->prepare($sql); 
$stmt->execute(); 
$row = $stmt->fetch();
$number_of_rows_before = $row['Rows'];
// Do your query here, afterwards
$stmt = $pdo->prepare($sql); 
$stmt->execute(); 
$row = $stmt->fetch();
$number_of_rows_after = $row['Rows'];
// If condition
if($number_of_rows_before == $number_of_rows_after) // Update was executed
else // a new row was inserted.

Upvotes: 1

Related Questions