How to properly delete a row using PHP & PDO
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.
Cyberized Ninja
Updated on June 05, 2022Comments
-
Cyberized Ninja almost 2 years
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; } ?>