CODEWITHSUNDEEP
phpmysql
Philipp M
Philipp M

Reputation: 3498

PHP/MYSQL - Check success/failure in case of INSERT INTO ... WHERE NOT EXISTS

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!'));     
}

Upvotes: 0

Views: 123

Answers (1)

AbraCadaver
AbraCadaver

Reputation: 78994

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.

Upvotes: 3

Related Questions