Delete an Account from database in PHP

Question

I am trying to delete an account when the admin clicks on Delete button in the following picture:

https://www.dropbox.com/s/ytqgdgk2c581yn3/Capture.JPG

Delete.php

<?php
$con=mysqli_connect("localhost","root","123","data1");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="Delete from users where id=id";

if (!mysqli_query($con,$sql))
{
die('Error: ' . mysqli_error($con));
}
echo "Record Deleted";
header("Refresh:3; url=admin.php");

mysqli_close($con);
?>

this is the code I used in the image

Admin.php

<?php
$con=mysqli_connect("localhost","root","123","data1");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM users");

echo "<table align='center' id='rounded-corner'>";
echo "<thead>
        <tr>
        <th scope='col' class='rounded-company'>ID</th>
        <th scope='col' class='rounded-company'>Username</th>
        <th scope='col' class='rounded-q1'>Password</th>
        <th scope='col' class='rounded-q2'>Filter Status</th>
        <th scope='col' class='rounded-q3'>Led Status</th>
        <th scope='col' class='rounded-q4'>Heater Status</th>
        <th scope='col' class='rounded-q4'>Edit</th>
        <th scope='col' class='rounded-q4'>Delete</th>
        </tr>
    </thead>";
    echo "<tfoot>";
echo "</tfoot>
<tbody>";

while($row = mysqli_fetch_array($result))
{
    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "<td>" . $row['password'] . "</td>";
echo "<td>" . $row['filter_st'] . "</td>";
echo "<td>" . $row['led_st'] . "</td>";
echo "<td>" . $row['heat_st'] . "</td>";
echo "<td>" . '<a class="button_green" href="edit.php">Edit</a>' . "</td>";
echo "<td>" . '<a class="button_red" href="delete.php">Delete</a>' . "</td>";
    echo "</tr>";
}
echo "</tbody>
</table>";

mysqli_close($con);
?>

Yes, how am I supposed to get the id for the specific user I want to delete when clicking on the Delete button? — MAX, Nov 15 '13 at 18:40
@user2997404 - do you have the code that generates the form in your screenshot? That's the code we need to see. — andrewsi, Nov 15 '13 at 18:42
@user2997404 - all you're doing at the moment is calling `delete.php`. You can pass in parameters by adding them to the URL, but it's a terribly insecure way of doing it, especially if you're then using it in a DELETE - consider what happens if google spiders your website and accesses each of those links. I'd recommend re-writing the initial form so that each delete link is a submit button in a `
`, so you can use $_POST to pass the IDs. — andrewsi, Nov 15 '13 at 18:51
From seeing your screenshot, it should be `where ID='$id' ";` your column is in uppercase - if that's the case in your DB @user2997404 — Funk Forty Niner, Nov 15 '13 at 18:52

score 2 · Answer 1 · answered Nov 15 '13 at 18:35

2

$sql="Delete from users where id=id";

id is not defined.

answered Nov 15 '13 at 18:35

Paul Dessert

6,363
8
47
74

score 2 · Answer 2 · answered Nov 15 '13 at 18:36

There is not terribly enough information to be able to give a full answer but one thing to look at would be this section:

$sql="Delete from users where id=id";

as it is, an id is always equal to itself, so you would be effectively deleting every user.

you need to differentiate the ids

$id = 10; //(get the id somehow)
$sql="Delete from users where id=". $id;

or more simply

$id = 10; //(get the id somehow)
$sql="Delete from users where id=$id";

score 2 · Answer 3 · answered Feb 25 '14 at 22:11

What you want first is to add the user id as parameter to your edit/delete actions (but be sure to enforce authorisations in those files); for delete this would give

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

Now in your Delete.php file you'll need to get the user id from the GET parameters

$id = intval($_GET['id']);

Note that you need to validate and despecialise the data you recover from the query parameters (in the $_GET array), and of course to ensure that only authorized users can effectively do those operations (so you need to check if the user requesting the deletion is authenticated and has the proper authorisations, you can't just assume that only admins will know the url).

Now as others have specified, you need to properly compute your delete query

"DELETE FROM users where id = " . $id;

Or with a prepared statement

$stmt = mysqli_prepare($con, "DELETE FROM users where id = ?;");
mysqli_stmt_bind_param($stmt, 'd', $id); // you bind one numeric parameter ('d') with $id as value
mysql_stmt_execute($stmt);

Edit: damn, just saw that this question I got to through the 'related' questions what 3 months old...

score 1 · Answer 4 · answered Nov 15 '13 at 18:36

1

The way you currently have it, it will delete all of your users. Since for every user, id will equal id.

It needs to be changed to

$sql="Delete from users where id=$id";

This way it will only delete the user where their id=$id

answered Nov 15 '13 at 18:36

Thornuko

287
1
4

Delete an Account from database in PHP

4 Answers4