Question 72330 in Mysql Question posted in
The official MySQL documentation can be found here.

Unable to delete multiple records from the database – Mysql

I’m having trouble with the datatype of the primary key attribute in the MySQL database when deleting multiple records. I’m trying to delete multiple records in the database table, which primary key attribute has "varchar" data type. I dont get any errors but records not deleting.

Then I change the datatype of the primary key attribute into "int" with "autoincrement" and try with the same code then it works correctly. and can’t figure out why. Can anyone see what’s causing this strange behavior? I am expecting to delete multiple records with the primary key attribute having the data type "varchar."

tblmproduct Table:

Name Type
Product_code (Primary) varchar(10)
LOT_no varchar(10)
Product_name varchar(50)
MFG_date date
EXP_date date
Supplier_id int(10)

html:

<span class="custom-checkbox">
<input type="checkbox" class="product_checkbox" data-product-id="<?php echo $row["Product_code"]; ?>">
<label for="checkbox2"></label>
</span>

Script:

$(document).on("click", "#delete_multiple", function () {
    var product = [];
    $(".product_checkbox:checked").each(function () {
      product.push($(this).data("product-id"));
    });
    if (product.length <= 0) {
      alert("Please select records.");
    } else {
      swal({
        title: "Are you sure?",
        text: "Once deleted, you will not be able to recover this Record !",
        icon: "warning",
        buttons: true,
        dangerMode: true,
      }).then((willDelete) => {
        if (willDelete) {
          var selected_values = product.join(",");
         
          $.ajax({
            type: "POST",
            url: "save-mproduct.php",
            cache: false,
            data: {
              type: 4,
              id: selected_values,
            },
            success: function (response) {
              var ids = response.split(",");
              for (var i = 0; i < ids.length; i++) {
                location.reload();
                $("#" + ids[i]).remove();
              }
            },
          });
          
          swal("Record Deleted successfully !", {
            icon: "success",
          });
  
        } else {
          swal("Your imaginary file is safe!");
        }
      });
    }
  });

save-mproduct.php:

if(count($_POST)>0){
    if($_POST['type']==4){
        $id=$_POST['id'];
        $sql = "DELETE FROM  tblmproduct WHERE Product_code IN ($id)";
        if (mysqli_query($con, $sql)) {
            echo $id;
        } 
        else {
            echo "Error: " . $sql . "<br>" . mysqli_error($con);
        }
        mysqli_close($con);
    }
}
Tags:

2

Answers


  1. 0

    Your code:

    $sql = "DELETE FROM  tblmproduct WHERE Product_code IN ($id)";

    Change to:

    $sql = "DELETE FROM  tblmproduct WHERE Product_code='$id'";
    Login or Signup to reply.
  2. 0

    Try this

    $id = $_POST['id'];
$arr_id = explode(',',$id);
$arr_id = array_map(function($e) {
    return "'".$e."'";
}, $arr_id);
$id = implode(',', $arr_id);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search