Delete Entry From MySQL Table Using AJAX

Question

As I said in another question in here, I'm creating a login class, but I'm making some additional options for it.

One of which is deleting users. I'll show you the code I'm using to do this.

First I list all the users using this function :

public function listUsers() {
    $sql = "SELECT username FROM user";
    if($stmt = $this->conn->prepare($sql)) {
        $stmt->bind_result($usrn);
        $stmt->execute();

        while ($row = $stmt->fetch()) {
            $stmt->bind_result($usrn);
            echo    '<form name="delete-user" id="'.$usrn.'" class="delete-user" method="post" action="#">'.
                        '<fieldset class="user-wrapper">'.
                            '<label for="user" class="user-label">User</label>'.
                            '<input type="text" name="user" class="user" value="'.$usrn.'" autocomplete="off" readonly="readonly" />'.
                        '</fieldset>'.
                        '<fieldset class="delete-wrapper">'.
                            '<button type="submit" name="delete" class="delete">Delete</button>'.
                        '</fieldset>'.
                    '</form>';
        }
        $stmt->close();
        echo '<div class="message-handling"></div>';
    }
        else {
            $error = true;
            $message['error'] = true;
            $message['message'] = "The Users Could Not Be Listed";
            return json_encode($message);       
        }
}

As you can see the result of this function in the HTML will be like this:

<form name="delete-user" id="altceva" class="delete-user" method="post" action="#"> 
    <fieldset class="user-wrapper">
        <label for="user" class="user-label">User</label>
        <input type="text" name="user" class="user" value="altceva" autocomplete="off" readonly="readonly" />
    </fieldset>
    <fieldset class="delete-wrapper">
        <button type="submit" name="delete" class="delete">Delete</button>
    </fieldset>
</form>

The id of the form is actually the name of each user (for each user a form is created with the user's id). Also the value of the input has the user name too, because I need it to pass it to another function so I can delete that user.

Next I'm trying to use AJAX to delete the user I want to delete. I have this function so far:

var ids = $('.delete-user-content form[id]').map(function(){
    return this.id;
}).get();

jQuery.each(ids, function() {
    console.log("#" + this + ' .delete');

    $("#" + this + ' .delete').click(function() {

    $('.delete-user-content .message-handling').fadeOut();

    $.ajax({
        type: 'POST',
        url: 'assets/class/login/process.php',
        dataType: 'json',
        data: {
            delusername:    $('.delete-user input.user').val()
        },
        success:function(data) {
            if(data.error === true) {
                $('.delete-user-content .message-handling').text(data.message).fadeIn();
                console.log(data.message);
                setTimeout(function () {
                    $('.delete-user-content .message-handling').fadeOut();
                }, 15000);
            }
                else {
                    $('.delete-user-content .message-handling').text(data.message).fadeIn();
                    console.log(data.message);
                    $('#'+id).fadeOut();
                    array.splice(index, 1);
                    setTimeout(function () {
                        $('.delete-user-content .message-handling').fadeOut();
                    }, 15000);
                }
        },
        error:function(XMLHttpRequest,textStatus,errorThrown) {
            $('.delete-user-content .message-handling').text('Error | Check PHP log for information').fadeIn();
            console.log('Error | Check PHP log for information');
        }
    });
    return false;

});
});

And the PHP function to process the action is this one:

public function deleteUser($user) {
    $sql = "DELETE FROM user WHERE username = '$user'";
    if($stmt = $this->conn->prepare($sql)) {
        $stmt->execute();
        $stmt->close();
        $error = false;
        $message['error'] = false;
        $message['message'] = "The User ".$user." Has Been Deleted";
        return json_encode($message);
    }
        else {
            $error = true;
            $message['error'] = true;
            $message['message'] = "The User Could Not Be Deleted";
            return json_encode($message);       
        }
}

But the thing is that this doesn't work properly. One problem would be if I delete the last listed user it will actually delete the first user. Then after I make one delete, if I click on another delete nothing else will be deleted, it will just tell me that the same user as the first time was deleted.

Now, I know this is a lot of code and it's hard to understand it, but could someone help me out with it?

Because of referential integrity, for historical tracking, statistics and other reasons, it is common not to actually DELETE records, but instead, to make them disabled or inactive. That might also solve part of your current problem. — DOK
– DOK, Commented Dec 28, 2011 at 14:09
OK. So how do I make them Inactive instead of Deleting them ? Also wouldn't this cause problems when I create a new user ? — Roland
– Roland, Commented Dec 28, 2011 at 14:14
Just a side-note: If you already put the paylod data ($user) into the sql query you have to sanitize/encode/escape that data. Prepared statements only free you from that part if you use placeholders/parameters and then "assign" the payload to those parameters. — VolkerK
– VolkerK, Commented Dec 28, 2011 at 14:18
@Roland Just add a field named status or summin use some sort of enumeration like inactvive, active, band, review set it to have a default and remember to query against it, then just update as appropriate. — T I
– T I, Commented Dec 28, 2011 at 14:19
To disable a record, or make it inactive, you add a boolean column to the database table. Then, instead of DELETE, you use UPDATE to toggle between 0 and 1, or Y and N. When you INSERT a new user, you enable them. This is a very common database design, used in many more areas than just users. Customers, products, tasks, whatever -- nothing ever really gets deleted without a trace. — DOK
– DOK, Commented Dec 28, 2011 at 15:42

jeroen · Accepted Answer · 2011-12-28 15:42:37Z

2

It seems you are using a very complicated way to determine your user ID. Why don't you just add a hidden field to the form and attach the event handler to the form? That would eliminate the need for the map and each, speeding everything up in the process.

So you form would be something like:

<form name="delete-user" class="delete-user" method="post" action="#"> 
  <input type="hidden" name="user_id" value="PHP_ECHO_USER_ID" />
  <fieldset class="user-wrapper">
  ...

And your jquery would be simplified to:

$("form.delete_user").submit(function() {
  var user_id = $(this).find('[name="user_id"]').val();    // on the top of my head, untested...
  console.log(user_id);     // make sure you get the right user ID before doing anything else
  ...
  return false;
});

edited Dec 28, 2011 at 15:42

answered Dec 28, 2011 at 14:23

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

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

4 Comments

Roland Over a year ago

Well, I keep trying different ways. But I get the same results. The thing is I have a form for each user in the table. And when I click on one of the delete it deletes the first entry in the table. Also, after one delete, all of the next delete return the same action and message, that the previous deleted user is deleted again. I can't seem to get this right ...

jeroen Over a year ago

@Roland it seems to me you are not getting the right user ID. If you are using the simplified version like I described in my answer, the first thing to do, is to add a console.log(user_id); after the line where it is set and exit the function without making the ajax call. As soon as you are sure the correct user ID is selected always, you can start adding the other stuff.

Roland Over a year ago

I'm using my version. Let me try yours.

Roland Over a year ago

It works now :) Thank you again @jeroen ! Though making all of these changes made me run in some other trouble with some previous question you answered here. It's about that change password to the user thing. I'll soon post the question here or if you want we can chat if you want to help me with that.

Collectives™ on Stack Overflow

Delete Entry From MySQL Table Using AJAX

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related