Deleting record if it exists in php/PDO

Question

I have some code that deletes a record from a MySQL database, but I get the success message even if the record does not exist. I have searched for ways of producing a message if the record does not exist but cannot find one that works with my code.

My code is:

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    /*** echo a message saying we have connected ***/
    //echo 'Connected to database<br />';
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "DELETE FROM ukgh WHERE telephone = :telephone";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':telephone', $telephone, PDO::PARAM_STR);   
    $stmt->execute(); 

    /*** close the database connection ***/
    $dbh = null;
}
catch(PDOException $e) {
    echo $e->getMessage();
}
// once processing is complete
// show success message
echo 'Success - The record for ' . $telephone . ' has been deleted.';
?>

Note to Fred-ii- The answer you referred to is correct, but the terminology in the question I asked was different. I asked how to ensure the RECORD I was deleting existed. I did not ask how to check if a row exists, and would never have thought to search for anything about an existing ROW when I was looking for an existing RECORD. To some experts like yourself a row may be the same as a record, but I and possibly other less enlightened people like me have never heard a record called a row. Best wishes, Tog

Why it is not success, if the record is not present? The Goal is reached, in both cases the record is not longer existend — Jens, Jun 01 '17 at 11:01
You have to check the affected rows: http://php.net/manual/de/pdostatement.rowcount.php — Jens, Jun 01 '17 at 11:01
You get the message because it is outside the TRY, place it in the TRY. — RiggsFolly, Jun 01 '17 at 11:02
I need to know that the record is there before I delete it. It seems that I am deleting a record that does not exist. — Tog Porter, Jun 01 '17 at 11:02
Also a DELETE that does not do a DELETE is not considered an error, just a query that did nothing — RiggsFolly, Jun 01 '17 at 11:03
See @Jens comment, and check the affected rows to work out if the delete did in fact do a delete — RiggsFolly, Jun 01 '17 at 11:04

score 4 · Accepted Answer · answered Jun 01 '17 at 11:05

Use rowCount(); inside try to check number of affected rows by your sql statement

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    /*     * * echo a message saying we have connected ** */
//echo 'Connected to database<br />';
    $sql = "DELETE FROM ukgh WHERE telephone = :telephone";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':telephone', $telephone, PDO::PARAM_STR);
    $stmt->execute();
    $count = $stmt->rowCount();// check affected rows using rowCount
    if ($count > 0) {
        echo 'Success - The record for ' . $telephone . ' has been deleted.';
    } else {
        echo "Your error message";
    }
} catch (PDOException $e) {
    echo $e->getMessage();
}

Read http://php.net/manual/en/pdostatement.rowcount.php

Deleting record if it exists in php/PDO

1 Answers1