PHP / MYSQL - 在INSERT INTO的情况下检查成功/失败...不存在

Question

I use the below query to add/insert a record only if an existing record with a certain value does not already exist:

php:

$query = "INSERT INTO accounts_mm (abc, name)
    SELECT '".$mysqli->real_escape_string($abc)."', '".$mysqli->real_escape_string($name)."' FROM DUAL  
    WHERE NOT EXISTS (
        SELECT abc FROM accounts_mm WHERE abc = '".$mysqli->real_escape_string($abc)."'
    )";

... it works ... but I always get 'Success' returned also in cases where no records were inserted.

if($mysqli->query($query)){
    echo json_encode(array('added' => 'Success!')); 
} else {
    echo json_encode(array('added' => 'Failure!'));     
}   

How can I check on success and failure for the above query?

Further attempts:

if(mysqli_affected_rows($mysqli->query($query)) > 0){
    echo json_encode(array('added' => 'Success!')); 
} else {
    echo json_encode(array('added' => 'Failure!'));     
}   

Answer 1

The query may succeed (not fail) but not insert because of WHERE NOT EXISTS. Just like a SELECT abc FROM accounts_mm WHERE abc = 1 will not generate an error but may not return results if there is no abc = 1. So you need to check the affected_rows:

Use the object as you had been using it:

$mysqli->query($query);

if($mysqli->affected_rows() > 0){
    echo json_encode(array('added' => 'Success!')); 
} else {
    echo json_encode(array('added' => 'Failure!'));     
}   

Or I guess you can combine them:

if($mysqli->query($query) && ($mysqli->affected_rows() > 0)){
    echo json_encode(array('added' => 'Success!')); 
} else {
    echo json_encode(array('added' => 'Failure!'));     
}   

Keep in mind, if $mysqli->query($query); returns false then the query didn't even execute due to an error, however if it returns true then the query executed but there may be no results.