How to tell if deletes in PDO worked

I just stumbled on an interesting thing as I worked with the PDO calls in my current program. The relevant portion of my program has the job of deleting a single row of a table by knowing the two values that comprise the primary key of the table. (In this particular table, the primary key is the combination of clientCode and orderNumber. As you know, a multi-column primary key means that the *combination* of the columns in the primary key have to be unique. Therefore, I could have many orders that have the same order number or the same client code but I can only have ONE row that has a given combination of clientCode and orderNumber.) That means that my delete can only remove at most one row from the table when I delete via the primary key. (It could also fail to delete any rows if no row containing the specific clientCode and orderNumber exists.)

I was very surprised to discover that the following code would let me delete a given row MULTIPLE times! That's right: you can delete a row (and it will be gone, as I've verified by querying the database directly) and then you can delete it *again* even though it is already gone!!

I've looked at the definition of the PDO execute statement in the PHP manual and it says that it returns true if it was successful and false if it was unsuccessful. In that case, shouldn't a failure to delete a row - because it isn't there - be deemed UNsuccessful? I'm here to tell you that PHP is deeming my repeated deletes of the same row SUCCESSFUL. That defies all logic in my opinion.

It seems very unlikely that PDO could actually work this way so I'm guessing I've somehow done something to inadvertently make failures get treated as successes but I really don't know what that would be. I'm hoping someone here can enlighten me.

I'm including complete sample code that demonstrates the problem. All you'll need is a copy of MySQL (or perhaps another database will work if the SQL is compatible) and then run this program.

If I *can't* figure out any way to make PDO tell me the truth about whether a delete worked or not, I suppose I'll have to do a second query to see if the row is indeed gone after the delete - and then hope that the second query doesn't lie to me too!

Here are the two files you'll need to try this yourself.

<?php

function getPdoConnection() {
  
    $debug = 0;

    /* The values used to connect to the database are stored in an external ini file. Read that
    * file and find the various values.
    */
    $file = "TW.ini";
  
    if (!$settings = parse_ini_file($file, TRUE)) throw new exception('Unable to open ' . $file . '.');

    $driver = $settings['database']['driver'];
    $host = $settings['database']['host'];
    $port = $settings['database']['port'];
    $username = $settings['database']['username'];
    $password = $settings['database']['password'];
    $dbname = $settings['database']['dbname'];
    $charset = $settings['database']['charset'];

    if ($debug) {
        echo "driver: $driver\n";
        echo "host: $host\n";
        echo "port: $port\n";
        echo "username: $username\n";
        echo "password: $password\n";
        echo "dbname: $dbname\n";
        echo "charset: $charset\n";
    }

    //Construct the DNS and options that are needed to connect to the database.
    $dns = "$driver:host=$host;port=$port;dbname=$dbname;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
        ];
    if ($debug) {
        echo "dns: $dns\n";
        echo "options:\n";
        print_r($options);
    }

    //Get a database connection.
    $pdo = new PDO($dns, $username, $password, $options);
  
    return $pdo;

}
?>

PHP:

<?php

    $debug = 0;
  
    if ($debug) echo "Entering ".__FILE__."\n";
  
    require_once dirname(__FILE__) . '/getPdoConnection.php';
  
    //Get a database connection.
    $pdo = getPDOConnection();
  
    //Create the Heroes table.
    if (createHeroesTable($pdo)) {
        echo "Heroes table was successfully created.\n";
    }
      
    //Get contents of Heroes table to verify that it is empty.
    $heroes = getHeroes($pdo);
    echo "Heroes array:\n";
    print_r($heroes);
  
    //Create some heroes.
    if (createHero($pdo, "Superman", "Kal-el", "5", "Justice League")) {
        echo "Hero Superman was successfully inserted.\n";
    }
    if (createHero($pdo, "Robin", "Dick Grayson", "4", "Justice League")) {
        echo "Hero Robin was successfully inserted.\n";
    }

    //Get contents of Heroes table to verify that the heroes were inserted.
    $heroes = getHeroes($pdo);
    echo "Heroes array:\n";
    print_r($heroes);
  
    //Determine the ID of a hero who needs to be updated.
    $heroID = getHeroID($pdo, "Superman");
    echo "The ID for Superman is: $heroID \n";
  
    //Update the hero's information.
    if (updateHero($pdo, $heroID, "Superman", "Clark Kent", "5", "Justice League")) {
        echo "Superman's info was updated.\n";
    }
  
    //Get contents of Heroes table to verify that the update took place.
    $heroes = getHeroes($pdo);
    echo "Heroes array:\n";
    print_r($heroes);
  
    //Delete the hero which was just updated (Superman).
    if (deleteHero($pdo, $heroID)) {
        echo "Superman was deleted.\n";
    }
  
    //Delete the hero (Superman) *again*.
    if (deleteHero($pdo, $heroID)) {
        echo "Superman was deleted *again*.\n";
    }
  
    //Get contents of Heroes table to verify that the delete took place.
    $heroes = getHeroes($pdo); //display the heroes table
    echo "Heroes array:\n";
    print_r($heroes);

    //Delete all of the heroes.
    if (deleteAllHeroes($pdo)) {
        echo "All rows of the Heroes table were successfully deleted.\n";
    }
  
    //Drop the Heroes table.
    if (dropHeroesTable($pdo)) {
        echo "Heroes table was successfully dropped.\n";
    }
  
    exit;
  
    function getHeroes($pdo) {
      
        global $debug;
      
        if ($debug) echo "Entering getHeroes()...\n";
      
        //Fetch the heroes from the table. There is going to be at least one, otherwise we would have exited.
        $sql = "select id, name, realname, rating, teamaffiliation from heroes";
        $stmt = $pdo->query($sql);
        $heroes = array(); //initialize heroes array which will hold the result set
        while ($row = $stmt->fetch()) { //if the query returns no rows, the while loop will never start
//            echo $row['id']."; ".$row['name']."; ".$row['realname']."; ".$row['rating']."; ".$row['teamaffiliation']."\n";
            array_push($heroes, $row);
            }
        if ($debug) {
            echo "heroes array:\n";
            print_r($heroes); 
        }
        return $heroes;
    }
  
    function getHeroID($pdo, $name) {
      
        global $debug;
      
        if ($debug) echo "Entering getHeroID($name)...\n";
      
        $sql = "select id from heroes where name = ?";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array($name));
    
        //Find the id for the hero and return it. The user selected the row from a GUI
        //and can only select a single existing hero at a time.
        while ($row = $stmt->fetch()) {
            $heroID = $row['id'];
        }
        return $heroID;
      
    }
  
    function createHero($pdo, $name, $realname, $rating, $teamaffiliation) {

        global $debug;
      
        if ($debug) echo "Entering createHeroes($name, $realname, $rating, $teamaffiliation)...\n";
      
        $sql = "INSERT INTO heroes (name, realname, rating, teamaffiliation) values (?,?,?,?)";
        $stmt = $pdo->prepare($sql);
        if($stmt->execute(array($name, $realname, $rating, $teamaffiliation))) return true;
        return false;
    }
  
    function updateHero($pdo, $id, $name, $realname, $rating, $teamaffiliation) {
      
        global $debug;
      
        if ($debug) echo "Entering updateHero($id)...\n";
      
        $sql = "UPDATE heroes SET name=?, realname=?, rating=?, teamaffiliation=? WHERE id=?";
        $stmt = $pdo->prepare($sql);
        if($stmt->execute(array($name, $realname, $rating, $teamaffiliation, $id))) return true;
        return false;
    }
  
    function deleteHero($pdo, $id) {
      
        global $debug;

        if ($debug) echo "Entering deleteHero($id)...\n";
      
        $sql = "delete from heroes where id=?";
        $stmt = $pdo->prepare($sql);
        if($stmt->execute(array($id))) return true;
        return false;
      
    }
  
    function deleteAllHeroes($pdo) {
      
        global $debug;
      
        if ($debug) echo "Entering deleteAllHeroes()...\n";
      
        $sql = "delete from heroes";
        $stmt = $pdo->prepare($sql);
        if($stmt->execute()) return true;
        return false;

    }
  
    function createHeroesTable($pdo) {
      
        global $debug;
      
        if ($debug) echo "Entering createHeroesTable()...\n";
      
        $sql = "create table heroes (
            id int(11) not null auto_increment,
            name varchar(30) collate utf8_unicode_ci not null,
            realname varchar(30) collate utf8_unicode_ci not null,
            rating int(11) not null,
            teamaffiliation varchar(30) collate utf8_unicode_ci not null,
            primary key(id))";
        $stmt = $pdo->prepare($sql);
        if ($stmt->execute()) return true;
        return false;
    }
  
    function dropHeroesTable($pdo) {
      
        global $debug;
      
        if ($debug) echo "Entering dropHeroesTable()...\n";
      
        $sql = "drop table heroes";
        $stmt = $pdo->prepare($sql);
        if ($stmt->execute()) return true;
        return false;     
    }
      
  
?>

PHP:

 

That is the way majority of SQL engines work. If delete statement does not fail it will return true regardless of how many records it deleted (which can be one or many). You can get the number of records deleted and send a false/true based on that if needed using rowcount:

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

 

Thank you, that is EXTREMELY helpful and saves me all kinds of grief!

I'm still surprised that things work that way but at least there is a straight-forward way to determine the impact of a delete (or update or insert, which I am working on next).

 

It is actually EASIER that way so you can tell the difference between the engine or query going bits-up face-down, or if an action occurred. Remember that PDOStatement->execute is used REGARDLESS of what you are running for a query, so trying to place the result of the query of any sort there makes no sense. Hence why if there's a result set you ->fetch and if it's a insert/delete/update you use ->rowcount.

It's only harder to grasp when you're starting out. you start getting into complex queries having that separation becomes a VERY welcome and even needed feature.