PHP check if MYSQL query was an insert or update

Solution:

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. 
Copy code

Solution:

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.

Solution:

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'