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?




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.

$stmt = $pdo->prepare($sql); 
$row = $stmt->fetch();
$number_of_rows_before = $row['Rows'];
// Do your query here, afterwards
$stmt = $pdo->prepare($sql); 
$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. 



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.


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."


This is the most reliable way to do it.




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'

People are also looking for solutions to the problem: Running PHP Selenium Webdriver tests programmatically, without phpunit command


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.