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?
Advertisement
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; }