Want to delete user in database in table PHP

Question

I have a table filled with data from my database, and i want a button next to it for each specific user to delete that user. but how do i do this?

Here's the code:

<?php               
    include '../includes/db_connect.php';

    $sql = "SELECT * FROM `users`";
    $result = mysql_query($sql, $connection) or die ("Failed to excecute the query $sql on $connection");
?>
<table border=1>
    <tr>
        <th>
        Access lvl
        </th>
        <th>
        Username
        </th>
        <th>
        Email
        </th>
    </tr>
<?php
    while($row = mysql_fetch_array($result))
    {
        echo "</td><td>";
        echo $row['access'];
        echo "</td><td>";
        echo $row['username'];
        echo "</td><td>";
        echo $row['email'];
        echo "</td></tr>";
    }
    echo "</table>";
?>

Answer 1

I do this very often and use jQuery for it. I use the following:

HTML:

<table>
....
<tr>
    <td><span class="delete">Delete me!</span></td>
</tr>
</table>

jQuery:

$(document).on("click", ".delete", function(event)
{
    var sData = "?id=" + $(this).data("id");
    $.ajax({
        url: "pages/delete_script.php",
        type: "POST",
        data: sData,
        success: function(sResult)
        {
            // Process the data you got back from the delete script
            // For example removing the row if successfully deleted:
            if(sResult == "OK")
            {
                $(this).closest("tr").remove();
            }
        },
        statusCode: 
        {
            404: function() 
            {
                alert("page not found");
            }
        }
    });
});

The on() is for the dynamic elements (I do everything with AJAX on that page). Then I put the data I need to send in the correct format and do the AJAX request. In the PHP file could be this:

if($_SERVER['REQUEST_METHOD'] == "POST")
{
    if(isset($_POST['id'])
    {
        mysql_query(
            sprintf('DELETE FROM `table` WHERE `id` = %d', $_POST['id'])
        );
    }
}

Of course it's recommended to use more checks to make sure you remove the right one.

Answer 2

Let the button redirect to an url like: example.com/user.php?delete=[id]

And fill the [id] with you $row['id']. Now, when you catch the parameter $_GET['delete'] you will get a id. Now you can simply throw this in your DELETE function in MYSQL.

Note:

• make sure you have security levels set for this action
• make sure you do not allow strings in this GET
• make sure you make use of mysql_real_escape_string() !! <- most important

Answer 3

I would move this away to an AJAX function, passing the variables to a JavaScript function via onClick or data-id="" and using jquery $('element').data('id'); to collect the id and fire it off to another static php file.

Once there you would want to check and sanitise the data that has been passed to the file before committing any user data to the SQL, as mentioned before:

• Use post as the AJAX method
• filter_var($var, FILTER_VALIDATE_INT)
• mysql_real_escape_string

This would feel better for user experience and also remove some of the risk of spiders triggering the action. People will still be able to hack it if your not hot on your security, but it makes it harder, and if you add a status column to you SQL rather than deleting the row you simply change its status, you can easily just bring the data back if ever you got hacked.