Deleting database rows in php & mysql

Question

I need some advice on how to dynamically target the rows I wish to delete as currently I am manually having to alter my php script to delete rows but am a little stuck on where to go next.

so here is my index of items:

<?php
$result = mysqli_query($con,"SELECT * FROM items");

while($row = mysqli_fetch_array($result)) {
  echo $row['added'] . $row['content']   . $row['id'];

  echo "<br>";

  echo "Mark as complete";

  echo "<br>";

  echo "<a href='delete.php'>Delete Item</a>";

  echo "<br>";

  echo "<a href='update.php'>Edit Item</a>";    

  echo "<br>";
  echo "<br>";     

}

mysqli_close($con);
?>

If I click on delete item it will only delete the one I have specified it to in my php here:

mysqli_query($con,"DELETE FROM items WHERE id='14'");

mysqli_close($con);

Now I need to know how to tell the button to delete the item that the link is associated to as you can I have manually entered 14 so that will delete that one. But I need some instruction or advice on how to delete the row or item id of that row in the database.

My initial thoughts are I am going to need to pass some information about this row perhaps using $_GET?

score 2 · Answer 1 · edited May 23 '17 at 12:15

You need to pass the ID of the item to be deleted in the URL of delete.php. First add the ID to the url:

echo '<a href="delete.php?id='. $row['id'] .'">Delete Item</a>';

Then, in delete.php you need to use $_GET to get the paramater from the URL, and insert that into the delete query:

$id =$_GET['id'];
$result = mysqli_query("DELETE FROM items WHERE id='$id'");

However, you need to be aware that anyone can then come along, type in a URL in the format 'delete.php?id=' and it will delete that item. You should:

Build in a confirmation method
Look into using prepared statements to prevent SQL injection attacks.

score 1 · Answer 2 · answered Jul 11 '14 at 12:55

1

Index of items page:

echo "<a href='delete.php?id=" . $row['id'] . "'>Delete Item</a>";

Delete file:

$id = $con->real_escape_string($_GET['id']); // preventing sql injections
$con->query("DELETE FROM items WHERE id='$id'");

answered Jul 11 '14 at 12:55

Jono20201

3,215
3
20
33

1

Glad I could help, remember to mark the question as complete :) – Jono20201 Jul 11 '14 at 12:58
`real_escape_string` is not the best solution to prevent SQL injections – ElendilTheTall Jul 11 '14 at 13:00
I am only learning at the moment so I am just trying to understand how and why things work so security is no issue for me at the moment. – user3725879 Jul 11 '14 at 13:04
Security should always be part of your learning. It is fundamental. – ssergei Jul 11 '14 at 19:57

score 0 · Answer 3 · answered Jul 11 '14 at 20:00

0

Secure solution:

    $id = $con->real_escape_string($_GET['id']);
    $sth = $con->prepare("DELETE FROM items WHERE id=?");
    $sth->bindParam(1, $id);
    $sth->execute();

answered Jul 11 '14 at 20:00

ssergei

1,289
9
21

Deleting database rows in php & mysql

3 Answers3