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 Mysqli.
Output
Create a Database in MySQL
The first step in this tutorial is the creation of a MySQL database. you can create tables by running SQL queries. Create a table ‘students’ in database using the following SQL query.
-- Database: `php_ajax_testing` -- -- Table structure for table `students` -- CREATE TABLE `students` ( `id` int(50) NOT NULL PRIMARY KEY AUTO_INCREAMENT, `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;
Create MySql Connection in PHP
For importing and exporting database in MySql will make a separate file ‘dbCongif.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:
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);
}
?>Creating HTML and jQuery AJAX
Now we will create a sample HTML page to show students table records after inserting in students table by fetching value from the database.
- Now we will create a sample HTML page to show students table records after inserting in students table by fetching value from the database.
- Add a checkbox to foreach loop through to show every particular row for check the checkbox and delete multiple records.
- 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>Display multiple records with checkbox
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 Records with PHP Mysql AJAX
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;
}
}
?>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.😊
