Delete Rows from MySQL with Bootstrap Confirm Modal

Datetime:2016-08-22 22:21:06          Topic: Bootstrap  MySQL           Share

Hi friends, in this tutorial post we will see How to delete rows from MySQL table, well it's a most common process of CRUD system, but as i titled this post we will set bootstrap and bootbox powered confirm dialog instead of javascript's default confirm dialog, in the terms of UI bootstrap gives far better UI than JavaScript Dialog, and getting confirmation on deleting the data is always good to make sure delete the data or not. i already posted a tutorial about the same but this was different from that, i have used here bootbox plugin for custom bootstrap dialog and the deleting process was done by jQuery Ajax with FadeOut effect. you can also check out the live demo of this simple tutorial so that you can get idea, that how all goes, so have a look at this tutorial and download the code from the given link and if you are using bootstrap for your projects so you should try this out, let's start coding.

Demo Script

Download Script

Note : 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.

Note taken from the official site.

Database Design / Table

Here is the Sample Product table with Dumping data, the database i have used here is named "dbtest", so create database in your PHPMyAdmin and name it whatever you want then copy paste the following sql code to create table with demo data.

--
-- Database: `dbtest`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_products`
--

CREATE TABLE IF NOT EXISTS `tbl_products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(35) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `tbl_products`
--

INSERT INTO `tbl_products` (`product_id`, `product_name`) VALUES
(1, 'Galaxy Jmax'),
(2, 'Killer Note5'),
(3, 'Asus ZenFone2'),
(4, 'Moto Xplay'),
(5, 'Lenovo Vibe k5 Plus'),
(6, 'Redme Note 3'),
(7, 'LeEco Le 2'),
(8, 'Apple iPhone 6S Plus');

Database Configuration

Common and Simple Database configuration/connection code with PDO extension, edit the following file as per your database credentials and save it as "dbconfig.php"

<?php
	
	$DBhost = "localhost";
	$DBuser = "root";
	$DBpass = "";
	$DBname = "dbtest";
	
	try{
		
		$DBcon = new PDO("mysql:host=$DBhost;dbname=$DBname",$DBuser,$DBpass);
		$DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
	}catch(PDOException $ex){
		
		die($ex->getMessage());
	}

Data Display from MySQL Table

Here is the Simple code to display the product details from MySQL tbl_products table, the last action column is for data deleting with anchor tag along with HTML5 custom Data Attribute data-id which stores product id and this will trigger out by delete_product class using jQuery's click event, using this we can get the product id to get deleted from table.

<table class="table table-bordered table-condensed table-hover table-striped">
        
        	<tr>
            	<th>#ID</th>
                <th>Product Name</th>
                <th>Action</th>
            </tr>
            
            
            <?php
			require_once 'dbconfig.php';
			$query = "SELECT product_id, product_name FROM tbl_products";
			$stmt = $DBcon->prepare( $query );
			$stmt->execute();
			while ($row=$stmt->fetch(PDO::FETCH_ASSOC) ) {
				extract($row);
				?>
                <tr>
                <td><?php echo $product_id; ?></td>
                <td><?php echo $product_name; ?></td>
                <td>
                <a class="delete_product" data-id="<?php echo $product_id; ?>" href="javascript:void(0)">
                <i class="glyphicon glyphicon-trash"></i>
                </a></td>
                </tr>
                <?php
			}
			?>
            
        
        </table>

following is the simple confirm dialog code, but i have used here bootbox custom dialog.

Confirm Dialog

bootbox.confirm("Are you sure?", function(result) {
   // delete code here
});

Custom Dialog

this is a custom dialog i have used here to do some ajax callback's mainly to delete the data using ajax method. in the delete button function i have make an ajax call to delete the current clicked row.

bootbox.dialog({
  message: "I am a custom dialog",
  title: "Custom title",
  buttons: {
    success: {
      label: "No!",
      className: "btn-success",
      callback: function() {
         // cancel button, close dialog box
      }
    },
    danger: {
      label: "Delete!",
      className: "btn-danger",
      callback: function() {
         // jquery ajax delete code here
      }
    }
  }
});

jQuery Code to Delete Data

jQuery Ajax method to delete clicked row without page refreshing, this should be within dialog's delete callback function, you can see the below complete code. if the row deleted another dialog will appear which is alert for row successful deletion and the row will fadeout.

$.ajax({		  
    type: 'POST',
    url: 'delete.php',
    data: 'delete='+pid		  
})
.done(function(response){
    bootbox.alert(response);
    parent.fadeOut('slow');					  
})
.fail(function(){
    bootbox.alert('Something Went Wrog ....');
})

using $.post to do the same(handle delete request)

$.post method to handle delete request

$.post('delete.php', { 'delete':pid })
.done(function(response){
   bootbox.alert(response);
   parent.fadeOut('slow');
})
.fail(function(){
   bootbox.alert('Something Went Wrog ....');
})

delete.php

this file will called silently via ajax and after getting the id of specific clicked row it will delete the row and displays the product deletion message in alert box as a response.

<?php

	require_once 'dbconfig.php';
	
	if ($_REQUEST['delete']) {
		
		$pid = $_REQUEST['delete'];
		$query = "DELETE FROM tbl_products WHERE product_id=:pid";
		$stmt = $DBcon->prepare( $query );
		$stmt->execute(array(':pid'=>$pid));
		
		if ($stmt) {
			echo "Product Deleted Successfully ...";
		}
		
	}

Complete jQuery Code

This is the main jQuery Code which will trigger the bootstrap confirm dialog on delete_product click event, don't worry it just looks little lengthy but it is so easy to understand, ok for the confirm dialog we have to use bootbox, it's a small JavaScript library to manage custom dialogs using bootstrap modals.

<script>
	$(document).ready(function(){
		
		$('.delete_product').click(function(e){
			
			e.preventDefault();
			
			var pid = $(this).attr('data-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.php',
						  data: 'delete='+pid
						  
					  })
					  .done(function(response){
						  
						  bootbox.alert(response);
						  parent.fadeOut('slow');
						  
					  })
					  .fail(function(){
						  
						  bootbox.alert('Something Went Wrog ....');
						  						  
					  })
					  					  
				  }
				}
			  }
			});
			
			
		});
		
	});
</script>

How it goes :

$('.delete_product').click() : product deleting click event.

var pid = $(this).attr('data-id'); : get product id.

var parent = $(this).parent("td").parent("tr"); : get the clicked <tr> row to fade out.

OutPut : Confirm Dialog

OutPut : Alert Dialog

Hope you guy's like this tutorial, and please don't forget to share this tutorial with you friends. if you are using bootstrap for your projects then you must use this, so download the code and try...





About List