How to properly delete a row using PHP & PDO

Question

I looked up multiple ways to delete a specified row in PDO, and I am a beginner in learning PHP/PDO. I originally used Mysql_query and the like, but I am now told that it is bad practice to use. I have a script to delete a news post by id when the delete button is pressed. I converted it from the original MySQL code to PDO, but in doing so I fear I misread something and messed up. I'm just wondering if this is a "secure" method of doing this or if I should be using MySQLI and the like.

Below is my script, if you can help me get it working that would be great, but I am more interested in the correct and proper way to delete a row in PHP/PDO.

 <?php
    $dbh = connectDb(); 

    if( isset($_POST['delete']) )
    {
        if( isset( $_POST['id'] ) && is_numeric( $_POST['id'] ) && $_POST['id'] > 0 )
        {
            $id = $_POST['id'];
            $stmt = $dbh->prepare( "DELETE FROM stats WHERE id =:id" );
            $stmt->bindParam(':id', $id);
            $stmt->execute();
        }
        else
        {
            echo "Invalid ID";
        }
    }
    $stmt = $dbh->query('SELECT * FROM news ORDER BY id ASC');

    while($stmt = $rows->fetch(PDO::FETCH_ASSOC)) {
        echo <<<_END
        <pre>
        Id: {$rows['id']}
        Title: {$rows['title']}
        Body: {$rows['body']}
        Date: {$rows['date']}
        </pre>
        <form action="rmposts.php" method="post">
        <input type="hidden" name="delete" value="yes" />
        <input type="hidden" name="id" value="{$rows['id']}" />
        <input type="submit" name="delete" value="DELETE NEWS" /></form>
    _END;
    }
    ?>

Answer 1

It seems that you don't set properly the $id variable. You have to understand that the php script receive all form values (including the id to deleted) in $_POST variable: any other value previously loaded is not maintained when you reload the script.

So, you have to check for $_POST['id'] and assigning its value to $id variable:

$dbh = connectDb(); 

if( isset($_POST['delete']) )
{
    if( isset( $_POST['id'] ) && is_numeric( $_POST['id'] ) && $_POST['id'] > 0 )
    {
        $id = $_POST['id'];
        $stmt = $dbh->prepare( "DELETE FROM news WHERE id =:id" );
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        if( ! $stmt->rowCount() ) echo "Deletion failed";
    }
    else
    {
        echo "ID must be a positive integer";
    }
}

First of all, we promote if( isset($_POST['delete']) ) to a wrapper for the full delete code: there is no reason to prepare the query if it is not executed. Also, if $_POST['delete'] is not set, it not means that deletion fails, but simply that deletion is not requested, so we remove the error alert.

Then, we check for $_POST['id'] validity (exists, is numeric, is positive), we assign it to $id variable and now we perform the query: through ->rowCount() we check the rows affected by the query and, if the result is 0, we display an error message.

---

Edit: additional errors:

Your query routine is wrong:

    $rows = dbh->query('SELECT * FROM news ORDER BY id ASC');
#   └─┬─┘
#     └─────────┐
#             ┌─┴─┐
while($rows = $stmt->fetch(PDO::FETCH_ASSOC)) {

Change above first line in:

$stmt = dbh->query('SELECT * FROM news ORDER BY id ASC');

Also your Heredoc syntax is wrong: inside <<<_END ... END you have to wrap array names by square brackets: {$rows['id']}, {$rows['title']}, etc...

Carefully note:

While debugging code, you have to check for errors:

And, in the future, don't forget to activate error checking in our php code:

error_reporting( E_ALL );
ini_set( 'display_errors', 1 );

With error checking, our original code produce this error:

Activate error reporting (error_reporting( E_ALL ); ini_set( 'display_errors', 1 ); at top of your script) and — if you got a 500 server error — look at your server error log: here you will see the detailed error, the file and the line in which error occurred.