PHP MySQL GET RESULT from INSERT INTO IF NOT EXIST

I know how to do INSERT INTO if the record doesn’t exist. But if it does exist, mysql doesn’t treat it as an error. It just treats it as 0 rows inserted. I want to know if that was the case and return that message to the user that the data they submitted already exists.

I know I can do it if I first query mysql before I attempt to insert the new data. However, is there a way to do it with the INSERT and get a result?

Answer

You can use ON DUPLICATE KEY UPDATE in which case mysqli::$affected_rows should return 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values (source). So your code should be something like:

if ($mysqli->affected_rows == 1) {
                    
    $inserted = true;
}

if ($mysqli->affected_rows == 2) {
        
    $updated = true;
}