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?

Community · Accepted Answer · 2017-05-23 12:15:00Z

2

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.

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Jul 11, 2014 at 13:00

ElendilTheTall

1,50219 silver badges27 bronze badges

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

Comments

Jono20201 · Accepted Answer · 2014-07-11 12:55:16Z

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, 2014 at 12:55

Jono20201

3,2053 gold badges22 silver badges34 bronze badges

4 Comments

Jono20201 Over a year ago

Glad I could help, remember to mark the question as complete :)

ElendilTheTall Over a year ago

real_escape_string is not the best solution to prevent SQL injections

user3725879 Over a year ago

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.

ssergei Over a year ago

Security should always be part of your learning. It is fundamental.

ssergei · Accepted Answer · 2014-07-11 20:00:22Z

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, 2014 at 20:00

ssergei

1,3099 silver badges21 bronze badges

Collectives™ on Stack Overflow

Deleting database rows in php & mysql

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related