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?

RESOLVED

Alright. I got it figured out!

I have a button that references the ID of the list item:

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

I then pass that ID to deletelist.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);
}

Item gets deleted.

Learn about prepared statements to prevent sql injection – Jens Jul 20 '17 at 05:28 — Jens, Jul 20 '17 at 05:28
You never execute the delete query – Jens Jul 20 '17 at 05:29 — Jens, Jul 20 '17 at 05:29

score 4 · Answer 1 · answered Jul 20 '17 at 05:31

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 '17 at 05:31

Bhargav Chudasama

6,928
5
21
39

score 3 · Answer 2 · answered Jul 20 '17 at 05:45

3

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 '17 at 05:45

sujivasagam

1,659
1
14
26

Should I be getting undefined index for I'd? – Marty Lavender Jul 20 '17 at 05:55
2

Be careful using user input from GET directly in a statement! Better use prepared statements with param binding. See https://stackoverflow.com/q/60174/1915746 – user1915746 Jul 20 '17 at 05:55

score 0 · Answer 3 · answered Jul 20 '17 at 19:07

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.

score -1 · Answer 4 · answered Jul 20 '17 at 06:07

-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 '17 at 06:07

vjy tiwari

843
1
5
17

*try like below* is not an answer. Explain what you have changed and why – Jens Jul 20 '17 at 06:35
@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 – vjy tiwari Jul 20 '17 at 07:26
`mysqli_real_escape_string` does not prevent SQL injection completly. – Jens Jul 20 '17 at 08:04
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. – vjy tiwari Jul 20 '17 at 09:59
read here: https://stackoverflow.com/questions/32391315/is-mysqli-real-escape-string-enough-to-avoid-sql-injection-or-other-sql-attack – Jens Jul 20 '17 at 10:00

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

4 Answers4