PHP MySQL GET RESULT from INSERT INTO IF NOT EXIST-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

You could use an else block to set a Return Message to the user

Use a parameter like below

@ReturnMessage varchar(max) output


If (Not Exists ...)
Begin
     Insert ....
End
Else
Begin
     Set @ReturnMessage = 'This Record already exists'
End