Delete MySQL Rows with Bootstrap Confirm Modal - Onlyxcodes - onlyxcodes

Sunday, 27 January 2019

Delete MySQL Rows with Bootstrap Confirm Modal - Onlyxcodes

In this tutorial, I’ll explain to you how to delete MySQL rows with bootstrap confirm modal using JSP.

The one option already available for record deletion with confirmation that is the default confirm dialog for JavaScript, but the bootstrap confirmation dialog gives better UI than the plain dialog for JavaScript. 

Before the record deleting the bootstrap confirmation modal is important because it provides the user with an opportunity to make clear that the record is deleted or not. If the user agrees then the modal will delete the record otherwise the record will be saved.

Here I used the bootbox library to build custom bootstrap confirmation dialog and record deletion procedure will be achieved with fade-out effect by jQuery Ajax method.

Let's generate this confirmation dialog, it's nice and enhances the UI / UX for website or web application as opposed to the default JavaScript dialog. 

Delete MySQL Rows with Bootstrap Confirm Modal

Table Content

1. Database and Table Design

2. Definition: Bootbox.js

3. Include Bootstrap CSS, JS, jQuery and bootbox library

4. Fetch Record from MySQL Table

5. Confirmation Dialog Box

6. Custom Dialog Box

7. Ajax Codes to Delete Record

8. delete.jsp

9. jQuery and Ajax Complete Codes

10. Output


1. Database and Table Design 

I use 'bootstrap_modal_confirm_delete_db' named database here and establish a table called a mobile, ok first you create a database under your PHPMyAdmin what name depends on you and copy and paste the below SQL code into your database to create a table.

CREATE TABLE `tbl_mobile` (
  `id` int(11) NOT NULL,
  `mobile_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

create mobile table in the database

I have inserted four dumping records to fast-inserted records in the table executing SQL codes below.  

INSERT INTO `tbl_mobile` (`id`, `mobile_name`) VALUES
(1, 'Apple iPhone X'),
(2, 'mi note 4'),
(3, 'OPPO F9'),
(4, 'VIVO V11');  

inserted dumping record in the table

2. Definition : Bootbox.js

Bootbox.js is a small JavaScript library which allows you to create programmatic dialog boxes using Bootstrap modals, without having to worry about creating, managing or removing any of the required DOM elements or JS event handlers. Here’s the simplest possible example: ( definition take from bootbox official site )

3. Include Bootstrap CSS, JS, jQuery and bootbox library

Bootstrap – Compulsory bootstrap package required for this project. New edition of the bootstrap 4 available on their official website.

I have created an index.jsp file. Before closing the < /head > tag, all library files jQuery, bootstrap and boot box warning library is required in this file for confirmation dialog paste.

<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css">
<script src="js/jquery-1.12.4-jquery.min.js" type="text/javascript"></script>
<script src="bootstrap/js/bootstrap.min.js" type="text/javascript"></script>
<script src="js/bootbox.min.js" type="text/javascript"></script> 

4. Fetch Record from MySQL Table

Below the code for retrieving mobile records from MySQL 'tbl_mobile' and putting it in tabular format. The last act element is for anchor tag records deletion along with specific Value Attribute. 

<td><a data_mobile_id="<%=rs.getInt("id")%>" href="javascript:void(0)" class="btn btn-sm btn-danger"><i class="glyphicon glyphicon-trash"></i></a></td> 

The attribute data_mobile_id that collected the mobile Id and that will activate the btn-danger class attribute using the jQuery click case. You can use that to get the mobile Id and to delete a specific record from the table. 

See full code for retrieving records that can be set to table format.   

 <table class="table table-striped table-bordered table-hover">
    <thead>
        <tr>
            <th>Mobile Name</th>
            <th>Delete</th>
        </tr>
    </thead>
    <%
        String dburl="jdbc:mysql://localhost:3306/bootstrap_modal_confirm_delete_db";         String dbusername="root"; //database username   
        String dbpassword =""; //database password
                                
        try
        {
            Class.forName("com.mysql.jdbc.Driver"); //load driver
            Connection con=DriverManager.getConnection(dburl,dbusername,dbpassword); //create connection
                                    
            PreparedStatement pstmt=null; //create statement 
                                    
            pstmt=con.prepareStatement("SELECT * FROM tbl_mobile"); //sql select query
            ResultSet rs=pstmt.executeQuery();
                                    
            while(rs.next())
            {
           %>
            <tbody>
                <tr>
                    <td><%=rs.getString("mobile_name")%></td>
                    <td><a data_mobile_id="<%=rs.getInt("id")%>" href="javascript:void(0)" class="btn btn-sm btn-danger"><i class="glyphicon glyphicon-trash"></i></a></td>
                </tr>
            </tbody>
            <%
            }
        }
        catch(Exception e)
        {
           e.printStackTrace();
        }
    %>
</table>

5. Confirmation Dialog Box

See the confirmation dialog below for the popup ordered from the JavaScript library bootbox. 

bootbox.confirm('Are you sure ?',function(response){
 //delete code beginning here 
}); 

6. Custom Dialog Box

See the custom dialog box below I outlined how it works to delete records.

$('.btn-danger').click() – Get attribute anchor tag class name btn-danger for click event success. 

var id = $(this).attr('data_mobile_id') – Get current mobile Id and return current table Id with attr() method. The current id hold by id variable. 

var parent = $(this).parent('td').parent('tr'); – This will cause a slow fade-out effect on the < tr > tag row when the record is deleted. 

Next, you see the custom dialog and two to callback functions. 

The first function, if you click No button, then the modal will hide your data and save it.

The second function, I build ajax method in this function for deleting a record by clicking an event on a particular row.

$(document).ready(function(){
    $('.btn-danger').click(function(e){
       e.preventDefault();
       var id = $(this).attr('data_mobile_id');
       var parent = $(this).parent('td').parent('tr');
       bootbox.dialog({
           message: 'My Custom Dialogue',
           title: "<i class='glyphicon glyphicon-trash'></i> Delete !",
           buttons:{
               success:{
                   label: 'No',
                   className: 'btn-success',
                   callback: function(){
                       //cancel button, close dialogue box 
               },
               danger:{
                   label: 'Delete!',
                   className: 'btn-danger',
                   callback: function(){
                       
                     //ajax delete code beginning here
                   }
               }   
           }
       });
    });      
}); 

7. Ajax Codes to Delete Record

Underneath Ajax full start codes should be in the second callback function. When the row is deleted then another popup occurs that is notified to the complete deletion of the row and also the row will fade out.

$.ajax({
    type: 'POST',
    url: 'delete.jsp',
    data: 'delete_id='+id
    })
    .done(function(data){
        bootbox.alert(data);
        parent.fadeOut('slow');   
    })
    .fail(function(){
        bootbox.alert('Error...');
    }); 

8. delete.jsp

This file will communicate peacefully from ajax and after obtaining the correct clicked row Id, this will delete the row and screen the data deletion message being a result in the alert box.

<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%
    if(request.getParameter("delete_id")!=null) 
    {
        int id=Integer.parseInt(request.getParameter("delete_id")); 
      
        String dburl="jdbc:mysql://localhost:3306/bootstrap_modal_confirm_delete_db"; 
        String dbusername="root"; //database username   
        String dbpassword =""; //database password
        
        try
        {
            Class.forName("com.mysql.jdbc.Driver"); //load driver
            Connection con=DriverManager.getConnection(dburl,dbusername,dbpassword); //create connection
                                    
            PreparedStatement pstmt=null; //create statement
            
            pstmt=con.prepareStatement("DELETE FROM tbl_mobile WHERE id=?"); 
            pstmt.setInt(1,id); 
            pstmt.executeUpdate(); //execute query
            
            out.print("Data Deleted Successfully...."); 
            
            pstmt.close(); //close statement
            con.close(); //close connection
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
%> 



9. jQuery Ajax Complete Codes

See below complete jQuery Ajax codes to activate the bootstrap confirmation dialog that I have discussed separately above.

<script type="text/javascript">
$(document).ready(function(){
    $('.btn-danger').click(function(e){
       e.preventDefault();
       var id = $(this).attr('data_mobile_id');
       var parent = $(this).parent('td').parent('tr');
       bootbox.dialog({
           message: 'Are you sure you want to Delete ?',
           title: "<i class='glyphicon glyphicon-trash'></i> Delete !",
           buttons:{
               success:{
                   label: 'No',
                   className: 'btn-success',
                   callback: function(){
                       $('.bootbox').modal('hide');
                   }
               },
               danger:{
                   label: 'Delete!',
                   className: 'btn-danger',
                   callback: function(){
                       $.ajax({
                          type: 'POST',
                          url: 'delete.jsp',
                          data: 'delete_id='+id
                       })
                        .done(function(data){
                            bootbox.alert(data);
                            parent.fadeOut('slow');   
                       })
                       .fail(function(){
                           bootbox.alert('Error...');
                       });
                   }
               }   
           }
       });
    });      
});
</script> 
 

10. Output: Confirmation Dialog

bootstrap confirmation dialog | Delete MySQL Rows with Bootstrap Confirm Modal

Record Deletion Alert Dialog

record deletion alert dialog | Delete MySQL Rows with Bootstrap Confirm Modal

Download Codes

No comments:

Post a Comment