Delete multiple record with checkboxes in PHP Mysqli using Jquery AJAX


Delete multiple record with checkboxes in PHP Mysqli using Jquery AJAX

Content of this blog

In this post I will show your How to delete multiple records with checkbox in PHP Mysql using jQuery AJAX. I show you how you can use Ajax and JQuery to delete multiple records with one click based on check boxes that are checked without refreshing the page. If the user wants to delete multiple tables, he can check the box for that table row and click the Delete button when the system asks Are you sure want to delete this records. If he clicks Ok, if no check box is selected, the system will display a warning message again. Please select at least one checkbox. If the user has selected one or more check boxes, the user can delete multiple rows of the table with our page refreshing. For this I use Ajax  in JQuery code. Use Ajax to delete the code from the delete.php file. Ajax Delete multiple data with checkboxes in PHP Jquery Mysql
Delete multiple record with checkboxes in PHP Mysqli using Jquery AJAX

Creating Database Table

--
-- Database: `php_ajax_testing`
--
-- Table structure for table `students`
--

CREATE TABLE `students` (
`id` int(50) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
`city_name` varchar(100) NOT NULL,
`otp` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Creating HTML (index.php) and jQuery AJAX 

  1. Now we will create a sample HTML page to show students table records after inserting in students table by fetching value from the database.
  2. Add a checkbox to foreach loop through to show every particular row for check the checkbox and delete multiple records.
  3. fetch the data from students table using jQuery AJAX.
index.php
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Delete multiple record with checkboxes in PHP Mysqli using Jquery AJAX</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container" style="margin-top: 50px;">
<h2 class="text-center"><b>Delete multiple record with checkboxes in PHP Mysqli using Jquery AJAX</b></h2><br>
<button type="button" class="btn btn-success btn-md" style="float:right;margin-bottom: 10px">Delete Record</button><br>
<div class="result">
<!--- Display record here --->
</div>
</div>
</body>
</html>

<!---jQuery ajax live search --->
<script type="text/javascript">
$(document).ready(function(){
// fetch data from table without reload/refresh page
loadData();
function loadData(query){
$.ajax({
url : "display.php",
type: "POST",
chache :false,
data:{query:query},
success:function(response){
$(".result").html(response);
}
});
}
// Delete multiple record
$(".btn-success").click(function(){

var id = [];
$(".delete-id:checked").each(function(){
id.push($(this).val());
element = this;
});

if (id.length > 0) {
if (confirm("Are you sure want to delete this records")) {
$.ajax({
url : "delete.php",
type: "POST",
cache:false,
data:{deleteId:id},
success:function(response){
if (response==1) {
alert("Record delete successfully");
loadData();
}else{
alert("Some thing went wrong try again");
}
}
});
}
}else{
alert("Please select atleast one checkbox");
}
});
});
</script>

Create Database connection 

dbConfig.php
<?php

// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "php_ajax_testing";

// Create database connection
$con = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}


?>
Display the records from the students table in PHP Msqli using jQuery AJAX.
display.php
<?php
// include database connection file

include "dbConfig.php";

// fetch data from student table..

$output = "";

$sql = "SELECT * FROM students ORDER BY id ASC";

$query = mysqli_query($con, $sql);

if (mysqli_num_rows($query) >
0) {
$output .= "<table class='table table-hover table-striped'>
<thead>
       <tr>
    <th>Id</th>
    <th>Firs name</th>
    <th>Last name</th>
    <th>Email</th>
    <th>City name</th>
    <th>Delete</th>
   </tr>
</thead>";
while ($row = mysqli_fetch_assoc($query)) {
$output .= "<tbody>
<tr>
         <td>{$row['id']}</td>
    <td>{$row['first_name']}</td>
    <td>{$row['last_name']}</td>
    <td>{$row['email']}</td>
    <td>{$row['city_name']}</td>
    <td><input type='checkbox' class='delete-id' value='{$row['id']}'></td>
</tr>
         </tbody>";
}
$output .="</table>";
echo $output;
}else{
echo "<h5>No record found</h5>";
}

?>
Delete.php
<?php
// include database connection file

include "dbConfig.php";

// delete data from student table..

if (isset($_POST['deleteId'])) {

$deleteId = $_POST['deleteId'];
// implode function break the array in to string
$deleteId = implode(',', $deleteId);

$query = "DELETE FROM students WHERE id IN($deleteId)";

$result = mysqli_query($con, $query);

if ($result === true) {
echo 1;
}else{
echo 0;
}

}

?>
Delete multiple record with checkboxes in PHP Mysqli using Jquery AJAX
You can always support by sharing on social media or recommending my blog to your friends and colleagues.
If you have any suggestions / problems about this tutorial, please comment on the  form below.😊


Leave a Reply