Trying to build a delete button in PHP for each row but there's an error

Question

I was trying with the following code in PHP for generating row and delete button. But instead of deleting the selected ones, it deletes from the bottom of the row.

<?php
$connection = mysqli_connect('localhost','root','','rohit');
{

    $query  = "SELECT * FROM user";
    $result = mysqli_query($connection,$query);

    while($row = mysqli_fetch_assoc($result)) {
        $id     = $row['id'];
        $name   = $row['name'];
        $lname  = $row['lname'];
        $dept   = $row['department'];
        $dob    = $row['DOB'];
        $doj    = $row['DOJ'];
        $mobile = $row['mobile'];
        $email  = $row['email'];
        $salary = $row['salary'];
        $gender = $row['gender'];


        echo "<tr>";
        echo "<td>{$id}</td>";
        echo "<td>{$name}</td>";
        echo "<td>{$lname}</td>";
        echo "<td>{$dept}</td>";
        echo "<td>{$dob}</td>";
        echo "<td>{$doj}</td>";
        echo "<td>{$mobile}</td>";
        echo "<td>{$email}</td>";
        echo "<td>{$salary}</td>";
        echo "<td>{$gender}</td>";
        echo '<td>' . '<form method="post"><button class="mb-1 mr-1 btn btn-primary" name="edit"><i class="fa fa-edit"></i></button>' .
            '<button class="mb-1 mr-1 btn btn-danger" name="delete"><i class="fa fa-times"></i></button></form>'
            . '</td>';
        echo "</tr>";

    }
}

?>
<?php
if (isset($_POST['delete'])) {
    $query1  = "DELETE FROM user ";
    $query1 .= "WHERE id = $id LIMIT 1";
    $result1 = mysqli_query($connection,$query1);

    if (!$result1) {
        die("FAILED" . mysqli_error($connection));
    }
}
?>

Any kind of help is appreaciated. Thanks in advance. :)

"WHERE id = $id LIMIT 1" - Where are you defining $id? You should also look into using parameterized prepared statements instead of injecting the user data directly into the queries like that. — M. Eriksson
– M. Eriksson, Commented Aug 23, 2019 at 5:29
Is the code for the delete button executed at all? I can see that there is a button for delete, but that form is using method="post" while your if-statement are looking for $_GET['delete']? — M. Eriksson
– M. Eriksson, Commented Aug 23, 2019 at 5:33
@MagnusEriksson sorry I was trying with a different code from a different post that's why I put the $_GET['delete'] their, there actually $_POST['delete'] and it deletes from the bottom. — Aakash Priyadarshi
– Aakash Priyadarshi, Commented Aug 23, 2019 at 5:51
Ok, but where are you defining the variable $id which you use in the query? I don't even see you passing any id through the form at all? — M. Eriksson
– M. Eriksson, Commented Aug 23, 2019 at 5:53
Unless there's a legal requirement, don't allow users to DELETE data. Instead, allow them to UPDATE a 'visibility' flag to be 0. — Strawberry
– Strawberry, Commented Aug 23, 2019 at 7:06

Joseph · Accepted Answer · 2019-08-23 06:10:17Z

2

In your form you need to pass action attribute with the id of row that you want to delete

so it would be something like that

<form method="post" action="<?= $_SERVER['PHP_SELF']?>?id={$id}">

then in your SQL query, you will pass this id and I use here prepared statement to protect you from SQL injection

<?php
  if (isset($_POST['delete'])) {
     $stmt = $connection->prepare("DELETE FROM user WHERE id=?");
     $stmt->bind_param($_GET['id']);
     $stmt->execute();

   if (!$result1) {
    die("FAILED" . mysqli_error($connection));
   }
 }
?>

edited Aug 23, 2019 at 6:10

answered Aug 23, 2019 at 6:00

Joseph

6,3273 gold badges24 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joseph Over a year ago

yah i know other users write that for him in comments, but at all, I will edit my answer to

Joseph Over a year ago

@MagnusEriksson i fix my answer i think it looks good now thanks for noticing

Jasper · Accepted Answer · 2019-08-23 05:59:43Z

I made some changes in your code. This will works..!!

1.changed method Attribute (Given form method POST , but in php query it is GET. Now both POST type)

2.passed id as hidden field at the time of delete. So it will delete ,correct record.Also delete query is change (use this id for delete the record)

<?php

$connection = mysqli_connect('localhost','root','','rohit');
{

$query  = "SELECT * FROM user";
$result = mysqli_query($connection,$query);

while($row = mysqli_fetch_assoc($result)) {
    $id     = $row['id'];
    $name   = $row['name'];
    $lname  = $row['lname'];
    $dept   = $row['department'];
    $dob    = $row['DOB'];
    $doj    = $row['DOJ'];
    $mobile = $row['mobile'];
    $email  = $row['email'];
    $salary = $row['salary'];
    $gender = $row['gender'];


    echo "<tr>";
    echo "<td>{$id}</td>";
    echo "<td>{$name}</td>";
    echo "<td>{$lname}</td>";
    echo "<td>{$dept}</td>";
    echo "<td>{$dob}</td>";
    echo "<td>{$doj}</td>";
    echo "<td>{$mobile}</td>";
    echo "<td>{$email}</td>";
    echo "<td>{$salary}</td>";
    echo "<td>{$gender}</td>";
    echo '<td>' . '<form method="post"><button class="mb-1 mr-1 btn btn-primary" name="edit"><i class="fa fa-edit"></i></button>' .
        '<input type="hidden" name="del_id" value="'.$id.'">'.
        '<button class="mb-1 mr-1 btn btn-danger" name="delete"><i class="fa fa-times"></i></button></form>'
        . '</td>';
    echo "</tr>";

}
}

?>
<?php
if (isset($_POST['delete'])) {
$del_id = $_POST['del_id'];
$query1  = "DELETE FROM user ";
$query1 .= "WHERE id = $del_id LIMIT 1";
$result1 = mysqli_query($connection,$query1);

if (!$result1) {
    die("FAILED" . mysqli_error($connection));
}
}
?>

Warning! This code is wide open for SQL injection attacks! It should use parameterized prepared statements instead of using completely unescaped user data directly in the queries like that.

JawadR1 · Accepted Answer · 2019-08-23 06:06:39Z

You are using the $id that you used to get data from $result object in while loop. The loop ended at the last present id in your database so it will only delete the last $id and only once. You can write $id of each record in your form. For example:

<form method="post"><button class="mb-1 mr-1 btn btn-primary" name="edit"><i class="fa fa-edit"></i></button>' .
    '<input display="hidden" name="id" value="'.$id.'">'.
    '<button class="mb-1 mr-1 btn btn-danger" name="delete"><i class="fa fa-times"></i></button>
</form>

and then in your PHP

if (isset($_POST['delete'])) {
    $d_id = $_POST['id'];
    $query1  = "DELETE FROM user ";
    $query1 .= "WHERE id = $d_id LIMIT 1";
    $result1 = mysqli_query($connection,$query1);
}

Collectives™ on Stack Overflow

Trying to build a delete button in PHP for each row but there's an error

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related