Delete Multiple Selected Data in PHP/MySQL

Question

Good day, I've seen a couple of threads with similar questions but I can't seem to implement the suggestions on my practice project.

is there any way I can add a function where my selected items can be deleted at once?

Here are my codes.

select.php

<?php  
 $connect = mysqli_connect("localhost", "root", "root", "appointments");  
 $output = '';  
 $sql = "SELECT * FROM appointments ORDER BY id DESC";  
 $result = mysqli_query($connect, $sql);  
 $output .= '  
      <div class="table-responsive">  
           <table class="table table-bordered">  
                <tr>  
                     <th width="5%">Checkbox</th>
                     <th width="10%">Id</th>  
                     <th width="40%">Name</th>  
                     <th width="40%">Email</th>  
                     <th width="40%">Address</th>  
                     <th width="10%">phoneNumber</th>  
                     <th width="10%">appointmentTime</th> 
                     <th width="10%">appointmentDate</th> 
                     <th width="50%">message</th> 
                     <th width="10%">delete</th> 
                </tr>';  
 if(mysqli_num_rows($result) > 0)  
 {  
      while($row = mysqli_fetch_array($result))  
      {  
           $output .= '


                <tr>  

                     </td>
                     <td><input type="checkbox" /></td>

                     <td>'.$row["id"].'</td>
                     <td class="name" data-id1="'.$row["id"].'" contenteditable>'.$row["name"].'</td>  
                     <td class="email" data-id2="'.$row["id"].'" contenteditable>'.$row["email"].'</td>
                     <td class="address" data-id2="'.$row["id"].'" contenteditable>'.$row["address"].'</td>  
                     <td class="phoneNumber" data-id2="'.$row["id"].'" contenteditable>'.$row["phoneNumber"].'</td>  
                     <td class="appointmentTime" data-id2="'.$row["id"].'" contenteditable>'.$row["appointmentTime"].'</td>  
                     <td class="appointmentDate" data-id2="'.$row["id"].'" contenteditable>'.$row["appointmentDate"].'</td>
                     <td class="message" data-id2="'.$row["id"].'" contenteditable>'.$row["message"].'</td>
                     <td><button type="button" name="delete_btn" data-id3="'.$row["id"].'" class="btn btn-danger                             btn_delete">Delete</button></td>   
                </tr>  
           ';  
      }  
 }  
 else  
 {  
      $output .= '<tr>  
                          <td colspan="10"><center><p style="color:red">No Data Found</p></center></td>  
                     </tr>';  
 }  
 $output .= '</table>  
      </div>';  
 echo $output;  
 ?>

Here's the delete function for a single row.

 <?php  
 $connect = mysqli_connect("localhost", "root", "root", "appointments");  
 $sql = "DELETE FROM appointments WHERE id = '".$_POST["id"]."'";  
 if(mysqli_query($connect, $sql))  
 {  
      echo 'Data Deleted';  
 }  
 ?>

Here's my display page.

<?php
    require("config.php");
    if(empty($_SESSION['user'])) 
    {
        header("Location: success.php");
        die("Redirecting to index.php"); 
    }
?>

<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>Simple Sidebar - Start Bootstrap Template</title>

    <!-- Bootstrap Core CSS -->
    <link href="css/bootstrap.min.css" rel="stylesheet">

    <!-- Custom CSS -->
    <link href="css/simple-sidebar.css" rel="stylesheet">
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script> 


</head>

<body>

    <div id="wrapper">

        <!-- Sidebar -->
        <div id="sidebar-wrapper">
            <ul class="sidebar-nav">
                <li class="sidebar-brand">
                    <a href="#">
                        Hope Medi Clinic
                    </a>
                </li>
                <li>
                    <a href="logout.php">Logout</a>
                </li>
                <li>
                    <a href="../../appointments/">Main Website</a>
                </li>
            </ul>
        </div>
        <!-- /#sidebar-wrapper -->

        <!-- Page Content -->
                   <div class="container">  
                <br />  
                <br />  
                <br />  
                <div class="table-responsive">  
                     <h3 align="center">Appointments</h3><br />  
                     <div id="live_data"></div>
                     <a href="#menu-toggle" class="btn btn-default" id="menu-toggle">Toggle Menu</a>
                </div>  
           </div>  
        <!-- /#page-content-wrapper -->

    </div>

    <script>  
 $(document).ready(function(){
      function fetch_data()  
      {  
           $.ajax({  
                url:"select.php",  
                method:"POST",  
                success:function(data){  
                     $('#live_data').html(data);  
                }  
           });  
      }  
      fetch_data();  


      function edit_data(id, text, column_name)  
      {  
           $.ajax({  
                url:"edit.php",  
                method:"POST",  
                data:{id:id, text:text, column_name:column_name},
                dataType:"text",  
                success:function(data){  
                     alert(data);  
                }  
           });  
      }  

     /* ............. */
      $(document).on('blur', '.name', function(){  
           var id = $(this).data("id1");  
           var name = $(this).text();  
           edit_data(id, name, "name");  
      });  

      $(document).on('blur', '.email', function(){  
           var id = $(this).data("id2");  
           var email = $(this).text();  
           edit_data(id, email, "email");  
      });
     $(document).on('blur', '.address', function(){  
           var id = $(this).data("id2");  
           var address = $(this).text();  
           edit_data(id, address, "address");  
      });
     $(document).on('blur', '.phoneNumber', function(){  
           var id = $(this).data("id2");  
           var phoneNumber = $(this).text();  
           edit_data(id, phoneNumber, "phoneNumber");  
      });
     $(document).on('blur', '.appointmentTime', function(){  
           var id = $(this).data("id2");  
           var appointmentTime = $(this).text();  
           edit_data(id, appointmentTime, "appointmentTime");  
      });
     $(document).on('blur', '.appointmentDate', function(){  
           var id = $(this).data("id2");  
           var appointmentDate = $(this).text();  
           edit_data(id, appointmentDate, "appointmentDate");  
      });
     $(document).on('blur', '.message', function(){  
           var id = $(this).data("id2");  
           var message = $(this).text();  
           edit_data(id, message, "message");  
      });

      $(document).on('click', '.btn_delete', function(){  
           var id=$(this).data("id3");  
           if(confirm("Are you sure you want to delete this?"))  
           {  
                $.ajax({  
                     url:"delete.php",  
                     method:"POST",  
                     data:{id:id},  
                     dataType:"text",  
                     success:function(data){  
                          alert(data);  
                          fetch_data();  
                     }  
                });  
           }  
      });  
 });

 </script>


</body>

</html>

oh. -.- sorry, but I doubt it would work with multiple lol . — ndg, May 20 '16 at 03:29
I think you have some problem with your data table. Check you use ` — Murad Hasan, May 20 '16 at 03:34
And this is not a form and a link so how can you operate the delete operation? — Murad Hasan, May 20 '16 at 03:36
The table functions over Javascript / AJAX. However, the second delete button is purely php, and it's for a singular row. The first button you see, is the button I want to use to delete the selected rows. — ndg, May 20 '16 at 04:05
both the button has same class name `btn_delete`, so how can you differ?? — Murad Hasan, May 20 '16 at 04:07
bro, like I said - the first button is nothing at the moment. I'll edit it to make it clear. :) — ndg, May 20 '16 at 04:10
write check box for each item and then get id from the check box. delete through the id. sounds simple. — NomanJaved, May 20 '16 at 04:10
The single row delete function is working. No need to fix that.. It's the multiple delete option I'm trying to get. — ndg, May 20 '16 at 04:14
@Frayne it already has a checkbox for each item :) . PS. the posted codes are just snippets of the entire thing. — ndg, May 20 '16 at 04:16
So what about _he first button is nothing at the moment._??? — Murad Hasan, May 20 '16 at 04:18

mickmackusa · Answer 1 · 2022-04-03T05:23:08.967

It will be cleaner and more professional to send an array of ids to your php file as suggested by earlier answers:

<input name="ids[]" value="<?php echo $id; ?>" type="checkbox">

...then make just one trip to the database to delete multiple rows.

if (
    empty($_POST['ids'])
    // || array_filter($_POST['ids'], function($v) { return !ctype_digit($v); })
) {
    exit('Missing/Invalid data submitted');  // be deliberately vague
}

$connect = new mysqli("localhost", "root", "root", "appointments");
$count = count($_POST['ids']);
$stmt = $connect->prepare(
    sprintf(
        "DELETE FROM appointments WHERE id IN (%s)",
        implode(',', array_fill(0, $count, '?'))  // e.g if 3 ids, then "?,?,?"
    )
);
$stmt->bind_param(str_repeat('i', $count), ...$_POST['ids']);
$stmt->execute();
printf('Deleted %d row(s)', $stmt->affected_rows());

This resembles a similar post of mine: SELECT with dynamic number of values in IN()

blazerunner44 · Accepted Answer · 2016-05-20T04:36:38.027

-1

I'm not sure exactly how you are sending the data from the HTML to the PHP page in this example, so I will give you a generic simple implementation and hopefully you can figure out how to work it into your project.

HTML:
Using checkboxes, you can send multiple values as an array to a php script like so.

<form action="delete.php" method="POST">
    <input name="delete[]" value="id_of_row" type="checkbox">
    <input name="delete[]" value="id_of_another_row" type="checkbox">
    <button type="submit">Submit</button>
</form>

This will send an array of whatever is in the value attribute of each box that is checked. You would then be able to delete every row that was checked with the following php script.

PHP:

<?php  
 $connect = mysqli_connect("localhost", "root", "root", "appointments");  
 foreach($_POST['delete'] as $id){
     $sql = "DELETE FROM appointments WHERE id = '" . $id . "';  
     if(mysqli_query($connect, $sql))  
     {  
          echo 'Data Deleted';  
     }
 }  
 ?>

This should be what you need to somehow implement into your existing project so that you can delete multiple rows at once.

edited May 20 '16 at 04:36

answered May 20 '16 at 04:20

blazerunner44

657
8
19

Sorry, I didn't post the main page. It's all AJAX / Jquery on the display page, so I opt not to post it :) The code posted above is called by AJAX to fill in a DIV. Just didn't post it because I needed a php solution, people might recommend javascript hehe – ndg May 20 '16 at 04:25
Btw, thanks - I'll try this in a sec and post my feedback. – ndg May 20 '16 at 04:27
doesn't work ;( maybe it's because I'm using ajax for the other functions (CRUD) – ndg May 23 '16 at 03:12
nvm, redid everything and added this :) Worked. thanks -- this leaves me with more questions lol . Anyway, thanks – ndg May 23 '16 at 07:04

score -1 · Answer 3 · answered May 20 '16 at 04:21

-1

You should be using array for this.

 <td><input type="checkbox" name='check[]' value=".$row['id']." /></td>

and in delete function you should be doing something like this.

<?php  
 $connect = new mysqli("localhost", "root", "root", "appointments"); 
 $totalCheckboxChecked = sizeof($_POST['check']);
      for($i=0;$i<$totalCheckboxChecked;$i++)
            {
             $idToDelete = $check[$i];
             $sql = "DELETE FROM appointments WHERE id = $idToDelete";  
             $result=$connect->query($sql);
    }

answered May 20 '16 at 04:21

Sanzeeb Aryal

4,358
3
20
43

Thanks! Mind if I ask? How should I do the button to delete? Seems like the buttons I make won't work. Maybe because I use javascript with the table? – ndg May 20 '16 at 07:54
You should have checkbox to all the values and a submit button so that you can check the values and submit. – Sanzeeb Aryal May 20 '16 at 08:09
damn still doesn't work, I might just redo everything for it to be compatible to a tutorial I'm trying -.- – ndg May 20 '16 at 08:48
I am not sure how you are submitting that checked data. Submit button is required i guess. – Sanzeeb Aryal May 20 '16 at 09:35
Change this : to @nathan – Sanzeeb Aryal May 20 '16 at 09:39
Sure, I'll try thanks. It's the ajax code that's submitting. – ndg May 23 '16 at 00:29

Delete Multiple Selected Data in PHP/MySQL

3 Answers3