Trying to delete specific row in a list of items from a MySQL database using PHP

Question

I have a list of items that is being output via PHP / MySQL. I also have an Edit button and a Delete button in one column. I am trying to figure out how to delete a list item on a specific row by clicking the Delete button. I have tried the following:

$id = $_GET['id'];

if(isset($_POST["deletelist"])) {

    $query = "SELECT * FROM lists";
    $result = mysqli_query($db, $query);
    if(mysqli_num_rows($result) == 1) {

    $query = "DELETE FROM lists WHERE id = '$id'";
} else {
        echo "Cannot delete";
    }
}

This of course does not work. Can anyone help me out with this?

UPDATE

This is the code for the entire page:

https://pastebin.com/raw/qjnZkUU2

UPDATED CODE

$id = $_GET['id'];

if(isset($_POST["deletelist"])) {

    $query = "SELECT * FROM lists";
    $result = mysqli_query($db, $query);
    if(mysqli_num_rows($result) == 1) {

    $query = "DELETE FROM lists WHERE id = '$id'";
    mysqli_query($db, $query);
} else {
        echo "Cannot delete";
    }
}

What I am confused about is how does the query know which item to delete? Should I be appending the ID to the URL to pass the ID?

UPDATE

Ok I think I get it....In the delete button, I need to echo the ID of that row so when the query runs from clicking the delete button, it knows which ID to delete correct?

Your code reads like an interview challenge. There are 5 really serious bugs in there and a lot of other issues. Learn how to solve one problem at a time and how to do it properly. — symcbean
– symcbean, Commented Aug 14 at 22:13

Bhargav Chudasama · Accepted Answer · 2017-07-20 05:31:00Z

4

you have to execute query for any action in database

mysqli_query($db, $query);

so execute a delete query and then try it again

answered Jul 20, 2017 at 5:31

Bhargav Chudasama

7,2256 gold badges24 silver badges41 bronze badges

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

Comments

sujivasagam · Accepted Answer · 2017-07-20 05:45:15Z

2

You have to execute the query. There is no query execution code. Try this

$id = $_GET['id'];

if(isset($_POST["deletelist"])) {

    $query = "SELECT * FROM lists";
    $result = mysqli_query($db, $query);
    if(mysqli_num_rows($result) == 1) {

    $query = "DELETE FROM lists WHERE id = '$id'";
    mysqli_query($db, $query);
} else {
        echo "Cannot delete";
    }
}

answered Jul 20, 2017 at 5:45

sujivasagam

1,7671 gold badge14 silver badges26 bronze badges

2 Comments

Marty Lavender Over a year ago

Should I be getting undefined index for I'd?

user1915746 Over a year ago

Be careful using user input from GET directly in a statement! Better use prepared statements with param binding. See stackoverflow.com/q/60174/1915746

Marty Lavender · Accepted Answer · 2017-07-20 19:07:07Z

In order to delete a specific row what I needed to do was echo the ID within a link/button. This would then pass the ID to the needed PHP to delete the row from the database.

Echoing the row ID in the button

echo "<a href='includes/deletelist.php?id=$row[id]'><input class=\"btn btn-danger\" value=Delete style=\"width: 85px;\"></a>

The PHP to delete the action row

<?php

include("db.php");

if (!isset($_GET['id'])) {
    echo 'No ID was given...';
    exit;
}
if ($db->connect_error) {
    die('Connect Error (' . $con->connect_errno . ') ' . $con->connect_error);
}
$sql = "DELETE FROM lists WHERE id = ?";
if (!$result = $db->prepare($sql)) {
    die('Query failed: (' . $db->errno . ') ' . $db->error);
}

if (!$result->bind_param('i', $_GET['id'])) {
    die('Binding parameters failed: (' . $result->errno . ') ' . $result->error);
}
if (!$result->execute()) {
    die('Execute failed: (' . $result->errno . ') ' . $result->error);
}
if ($result->affected_rows > 0) {
    echo "The ID was deleted with success.";
} else {
    echo "Couldn't delete the ID."; }
$result->close();
$db->close();
header('Location: ../account.php');
?>

This deletes the item row and then returns the user to account.php. Which in this case, the page never really changes.

vjy tiwari · Accepted Answer · 2017-07-20 06:07:57Z

-1

Try like below

if(isset($_POST["deletelist"]) && isset($_GET['id']) ) {
    $id = $_GET['id'];
    $query = "SELECT * FROM lists";
    $result = mysqli_query($db, $query);
    if(mysqli_num_rows($result) == 1) {

         $query = "DELETE FROM lists WHERE id = '".mysqli_real_escape_string($db,$id)."'";
        mysqli_query($db, $query);
    } else {
        echo "Cannot delete";
    }
}

answered Jul 20, 2017 at 6:07

vjy tiwari

8611 gold badge5 silver badges19 bronze badges

5 Comments

Jens Over a year ago

try like below is not an answer. Explain what you have changed and why

vjy tiwari Over a year ago

@Jens mysqli_real_escape_string need to use for remove the mysql ejection . and mysqli_query($db , $query) need to execute for any mysql action. Thanks

Jens Over a year ago

mysqli_real_escape_string does not prevent SQL injection completly.

vjy tiwari Over a year ago

This function add an escape character, the backslash, \, before certainly dangerous characters in a string passed in to this function. SO we can prevent this issue by using this function.

Jens Over a year ago

read here: stackoverflow.com/questions/32391315/…

Collectives™ on Stack Overflow

Trying to delete specific row in a list of items from a MySQL database using PHP

4 Answers 4

Comments

2 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related