$.ajax does not delete multiple table rows with checkbox

Question

I'm currently struggling as to why my code does not do the following multiple table row deletion php code:

incude("dbconnect.php");

if (isset($_POST["id"])) {

    foreach ($_POST["id"] as $id) {

        $sql = "DELETE FROM doctor WHERE DOC_LICENSE_NUM='.$id.'";

        if ($mydatabase->mysqli_query($sql) === TRUE) {
            echo "<div>Record deleted successfully</div>";
        } else {
            echo "Error deleting record: " . $mydatabase->error;
        }
        //$sql="DELETE from DOCTOR where DOC_LICENSE_NUM='".$id."'";
        //mysqli->query($connect, $sql);
    }
    //mysqli->close();
}

whereas dbconnect links $mydatabase to the sql. Now, I created a button with the id btn_delete in order to use javascript click function in handling multiple rows to delete in my sql.

<div style="float: right; margin: 0px 10px 10px 0px;"><button type="button" name="btn_delete" class="btn" id="btn_delete" ><span class="fa fa-trash" style="font-size:16px; margin-right:5px;"></span>Delete Multiple</button></div>

What I did is that I used $.ajax in order to gather the checked checkboxes in an array and using the url for my php for deleting rows in the table and if it has entered the php it will proceed to fade the rows out of the table.

$('#btn_delete').click(function(){
    if(confirm("Are you sure you want to delete these?")){
        var id=[];
        $(':checkbox:checked').each(function(i){
            id[i]=$(this).val();
            //alert(id);
        });

        if(id.length===0){
            alert("Please select at least two checkboxes");
        }else{
            $.ajax({
                url:'muldel.php',
                method: 'POST',
                data: {id:id},
                success: function(){
                    for (var i=0; i<id.length; i++){
                        $('tr#'+id[i]+'').css('background-color', '#ccc');
                        $('tr#'+id[i]+'').fadeOut('slow');
                    }
                    //location.reload();
                }

            });
        }
    }else{
        return false;
    }
});

However, when it proceeds to fade the row out of my tables, after I refresh it, it does not delete the rows like how I expected it to. What am I missing out here? (I forgot to mention that I used a unique entity key as my id)

Your query is wrong to delete multiple data from ids you should use `in` instead of `=`, Secondly you need to json_decode in your php and implode it as a string to perform `in` or loop through your whole array with `=` — Saad Suri, Nov 13 '17 at 08:50
@AmrAly but he is not using `json_decode` to convert it as an array — Saad Suri, Nov 13 '17 at 08:53
@user311699 you can't send a array directly to server from client side you have encode your array with JSON.stringify — Prasanna Venkatesh, Nov 13 '17 at 09:16

Jomy Joseph · Answer 1 · 2017-11-13T09:49:33.697

From your jquery you are posting id as coma separated ie. 1,2,3,4 etc. This should convert to array in php side array(1,2,3,4) using explode(); or make array($_POST["id"]) then put this in your foreach loop.

foreach ($array as $id) {

}

Hope this helps

or alternate way without foreach in php, you can use IN() in mysql. Check complete code if intersted. http://www.phpzag.com/delete-multiple-rows-with-checkbox-using-jquery-php-mysql/

Ikhlak S. · Answer 2 · 2017-11-13T09:55:06.093

0

Since you are passing an array from your Ajax call, you will need to build a SQL query using that:

$sql = "DELETE FROM doctor WHERE DOC_LICENSE_NUM IN (" . implode(",", $_POST['id']) . ".)";

// Execute 
 if ($mydatabase->mysqli_query($sql) === TRUE) {
     echo "<div>Record deleted successfully</div>";
 } else {
    echo "Error deleting record: " . $mydatabase->error;
 }

Note: You're open to SQL Injection. Always use prepared statements when your query requires parameters by others for it to execute. This could be a little help.

edited Nov 13 '17 at 09:55

answered Nov 13 '17 at 09:44

Ikhlak S.

8,578
10
57
77

what does `===` means in sql query, it should be `==` – KUMAR Jun 26 '20 at 01:33

Pratik Gadoya · Answer 3 · 2017-11-13T09:52:26.867

-1

You can use below query to delete multiple records.

$sql = "DELETE FROM doctor WHERE DOC_LICENSE_NUM IN (" . implode(',', $_POST['id']) . ")";
if ($mydatabase->mysqli_query($sql) === TRUE) {
    echo "<div>Record deleted successfully</div>";
} else {
    echo "Error deleting record: " . $mydatabase->error;
}

There is no need to iterate loop.

edited Nov 13 '17 at 09:52

answered Nov 13 '17 at 09:46

Pratik Gadoya

1,420
1
16
27

what does `===` means in sql query, it should be `==` – KUMAR Jun 26 '20 at 01:36

$.ajax does not delete multiple table rows with checkbox

3 Answers3