Friday, 1 April 2016

Ajax Delete multiple data with checkboxes in PHP Jquery Mysql



In this post I show you delete multiple records from mysql database on the basis of selected checkbox in php. You have show lots of tutorial on this things but today I have show you how to use Ajax and JQuery for delete multiple records on the basis of checked checkboxes on one single click without page refresh. When user want to remove multiple table show he can checked checkbox of that table row and click on delete button when system will ask you do you want to delete this records if he click yes then it again validate that use has checked check box or not if he not select any check box then system will again display alert message regarding please select atleast one checkbox and if user has select one or multiple checkboxes then user can remove multiple table row with background color change and fade out effect with out page refresh. For this things I have used ajax function call in jquery code. With help of Ajax it execute delete code from delete.php file. So, this is my tutorial on how to use Ajax to delete Multiple records from database with change background color of table and fade out effect without page refresh.

Source Code

Database


 CREATE TABLE IF NOT EXISTS `tbl_customer` (  
  `CustomerID` int(11) NOT NULL AUTO_INCREMENT,  
  `CustomerName` varchar(250) NOT NULL,  
  `Address` text NOT NULL,  
  `City` varchar(250) NOT NULL,  
  `PostalCode` varchar(30) NOT NULL,  
  `Country` varchar(100) NOT NULL,  
  PRIMARY KEY (`CustomerID`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;  
 --  
 -- Dumping data for table `tbl_customer`  
 --  
 INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Address`, `City`, `PostalCode`, `Country`) VALUES  
 (1, 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),  
 (2, 'Ana Trujillo', 'Avda. de la Constitución 2222', 'México D.F.', '05021', 'Mexico'),  
 (3, 'Antonio Moreno', 'Mataderos 2312', 'México D.F.', '05023', 'Mexico'),  
 (4, 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),  
 (5, 'Christina Berglund', 'Berguvsvägen 8', 'Lule', 'S-958 22', 'Sweden'),  
 (6, 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', '90110', 'Finland'),  
 (7, 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),  
 (8, 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA'),  
 (9, 'Matti Karttunen', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),  
 (10, 'Zbyszek', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),  
 (11, 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');  

multiple_delete.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 $sql = "SELECT * FROM tbl_customer";  
 $result = mysqli_query($connect, $sql);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial</title>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
           <script src="jquery.js"></script>  
           <style>  
                #box  
                {  
                     width:600px;  
                     background:gray;  
                     color:white;  
                     margin:0 auto;  
                     padding:10px;  
                     text-align:center;  
                }  
           </style>  
      </head>  
      <body>  
           <div class="container">  
                <br />  
                <h3 align="center">Delete multiple rows by selecting checkboxes using Ajax Jquery with PHP</h3><br />  
                <?php  
                if(mysqli_num_rows($result) > 0)  
                {  
                ?>  
                <div class="table-responsive">  
                     <table class="table table-bordered">  
                          <tr>  
                               <th>Customer Name</th>  
                               <th>Customer Address</th>  
                               <th>Delete</th>  
                          </tr>  
                <?php  
                     while($row = mysqli_fetch_array($result))  
                     {  
                ?>  
                          <tr id="<?php echo $row["CustomerID"]; ?>">  
                               <td><?php echo $row["CustomerName"]; ?></td>  
                               <td><?php echo $row["Address"]; ?></td>  
                               <td><input type="checkbox" name="customer_id[]" class="delete_customer" value="<?php echo $row["CustomerID"]; ?>" /></td>  
                          </tr>  
                <?php  
                     }  
                ?>  
                     </table>  
                </div>  
                <?php  
                }  
                ?>  
                <div align="center">  
                     <button type="button" name="btn_delete" id="btn_delete" class="btn btn-success">Delete</button>  
                </div>  
      </body>  
 </html>  
 <script>  
 $(document).ready(function(){  
      $('#btn_delete').click(function(){  
           if(confirm("Are you sure you want to delete this?"))  
           {  
                var id = [];  
                $(':checkbox:checked').each(function(i){  
                     id[i] = $(this).val();  
                });  
                if(id.length === 0) //tell you if the array is empty  
                {  
                     alert("Please Select atleast one checkbox");  
                }  
                else  
                {  
                     $.ajax({  
                          url:'delete.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');  
                               }  
                          }  
                     });  
                }  
           }  
           else  
           {  
                return false;  
           }  
      });  
 });  
 </script>  

delete.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 if(isset($_POST["id"]))  
 {  
      foreach($_POST["id"] as $id)  
      {  
           $sql = "DELETE FROM tbl_customer WHERE CustomerID = '".$id."'";  
           mysqli_query($connect, $sql);  
      }  
 }  
 ?>  

2 comments:

  1. can you make a tutorial on , auto complete . example i have an id number and then , if I enter my Id number on first input field , then my other information will automatically fill those input fields without refreshing the page XD , can you help me Sir plss...

    ReplyDelete