Writing PDO DELETE query values

Question

I was wondering if we can still use values from a freshly DELETE row as a SELECT or do we really need to SELECT it before ?

Example :
Transform this

$foo = $db->prepare("SELECT * FROM table WHERE id= :id");
$foo->execute(array(
    "id" => $table_id
));
$foo = $foo->fetch(PDO::FETCH_ASSOC);

$delete_foo = $bdd->prepare("DELETE FROM table WHERE id = :id");
$delete_foo->execute(array(
    "id" => $table_id
));
echo $foo['name'] . " has been deleted !";

Into this :

$delete_foo = $bdd->prepare("DELETE FROM table WHERE id = :id");
$delete_foo->execute(array(
    "id" => $table_id
));
$delete_foo = $delete_foo->fetch(PDO::FETCH_ASSOC);
echo $delete_foo['name'] . " has been deleted !";

It would be easier. I was just wondering, I use the 1st method but it just went in mind and I don't find answers.

Answer 1

In postgresql, there is a proprietary extension to the delete statement called RETURNING. Sql Server provides something similar, they call it OUTPUT

For example, OUTPUT DELETED.* in the following DELETE statement returns all columns deleted from the ShoppingCartItem table:

DELETE Sales.ShoppingCartItem  
    OUTPUT DELETED.*;  

Unfortunately, mysql does not have anything like the above. If you delete a row it's gone (unless you roll back the transaction instead of commiting). If you want to Select the data, you need to execute the SELECT before the DELETE

Answer 2

For the example provided, an extra select query just makes no sense. As you have your $value already.

I would rather say that you need to simplify your PDO code at whole. Compare the below code snippet with yours

$foo = $db->run("SELECT foo FROM table WHERE value = ?", [$value])->fetchColumn();
$db->run("DELETE FROM table WHERE value = ?", [$value]);
echo "$foo has been deleted!";

the run() function can be achieved by a very small PDO modification:

class MyPDO extends PDO
{
    public function run($sql, $args = NULL)
    {
        $stmt = $this->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

the code is taken from my article, Simple yet efficient PDO wrapper

Answer 3

DELETE queries wont return any results (besides rows affected), so a PDO::query wont have any usable data to fetch.