'Delete Multiple Selected Data in PHP/MySQL [duplicate]
Good day, I've seen a couple of threads with similar questions but I can't seem to implement the suggestions on my practice project.
is there any way I can add a function where my selected items can be deleted at once?
Here are my codes.
select.php
<?php
$connect = mysqli_connect("localhost", "root", "root", "appointments");
$output = '';
$sql = "SELECT * FROM appointments ORDER BY id DESC";
$result = mysqli_query($connect, $sql);
$output .= '
<div class="table-responsive">
<table class="table table-bordered">
<tr>
<th width="5%">Checkbox</th>
<th width="10%">Id</th>
<th width="40%">Name</th>
<th width="40%">Email</th>
<th width="40%">Address</th>
<th width="10%">phoneNumber</th>
<th width="10%">appointmentTime</th>
<th width="10%">appointmentDate</th>
<th width="50%">message</th>
<th width="10%">delete</th>
</tr>';
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
</td>
<td><input type="checkbox" /></td>
<td>'.$row["id"].'</td>
<td class="name" data-id1="'.$row["id"].'" contenteditable>'.$row["name"].'</td>
<td class="email" data-id2="'.$row["id"].'" contenteditable>'.$row["email"].'</td>
<td class="address" data-id2="'.$row["id"].'" contenteditable>'.$row["address"].'</td>
<td class="phoneNumber" data-id2="'.$row["id"].'" contenteditable>'.$row["phoneNumber"].'</td>
<td class="appointmentTime" data-id2="'.$row["id"].'" contenteditable>'.$row["appointmentTime"].'</td>
<td class="appointmentDate" data-id2="'.$row["id"].'" contenteditable>'.$row["appointmentDate"].'</td>
<td class="message" data-id2="'.$row["id"].'" contenteditable>'.$row["message"].'</td>
<td><button type="button" name="delete_btn" data-id3="'.$row["id"].'" class="btn btn-danger btn_delete">Delete</button></td>
</tr>
';
}
}
else
{
$output .= '<tr>
<td colspan="10"><center><p style="color:red">No Data Found</p></center></td>
</tr>';
}
$output .= '</table>
</div>';
echo $output;
?>
Here's the delete function for a single row.
<?php
$connect = mysqli_connect("localhost", "root", "root", "appointments");
$sql = "DELETE FROM appointments WHERE id = '".$_POST["id"]."'";
if(mysqli_query($connect, $sql))
{
echo 'Data Deleted';
}
?>
Here's my display page.
<?php
require("config.php");
if(empty($_SESSION['user']))
{
header("Location: success.php");
die("Redirecting to index.php");
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="author" content="">
<title>Simple Sidebar - Start Bootstrap Template</title>
<!-- Bootstrap Core CSS -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- Custom CSS -->
<link href="css/simple-sidebar.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
</head>
<body>
<div id="wrapper">
<!-- Sidebar -->
<div id="sidebar-wrapper">
<ul class="sidebar-nav">
<li class="sidebar-brand">
<a href="#">
Hope Medi Clinic
</a>
</li>
<li>
<a href="logout.php">Logout</a>
</li>
<li>
<a href="../../appointments/">Main Website</a>
</li>
</ul>
</div>
<!-- /#sidebar-wrapper -->
<!-- Page Content -->
<div class="container">
<br />
<br />
<br />
<div class="table-responsive">
<h3 align="center">Appointments</h3><br />
<div id="live_data"></div>
<a href="#menu-toggle" class="btn btn-default" id="menu-toggle">Toggle Menu</a>
</div>
</div>
<!-- /#page-content-wrapper -->
</div>
<script>
$(document).ready(function(){
function fetch_data()
{
$.ajax({
url:"select.php",
method:"POST",
success:function(data){
$('#live_data').html(data);
}
});
}
fetch_data();
function edit_data(id, text, column_name)
{
$.ajax({
url:"edit.php",
method:"POST",
data:{id:id, text:text, column_name:column_name},
dataType:"text",
success:function(data){
alert(data);
}
});
}
/* ............. */
$(document).on('blur', '.name', function(){
var id = $(this).data("id1");
var name = $(this).text();
edit_data(id, name, "name");
});
$(document).on('blur', '.email', function(){
var id = $(this).data("id2");
var email = $(this).text();
edit_data(id, email, "email");
});
$(document).on('blur', '.address', function(){
var id = $(this).data("id2");
var address = $(this).text();
edit_data(id, address, "address");
});
$(document).on('blur', '.phoneNumber', function(){
var id = $(this).data("id2");
var phoneNumber = $(this).text();
edit_data(id, phoneNumber, "phoneNumber");
});
$(document).on('blur', '.appointmentTime', function(){
var id = $(this).data("id2");
var appointmentTime = $(this).text();
edit_data(id, appointmentTime, "appointmentTime");
});
$(document).on('blur', '.appointmentDate', function(){
var id = $(this).data("id2");
var appointmentDate = $(this).text();
edit_data(id, appointmentDate, "appointmentDate");
});
$(document).on('blur', '.message', function(){
var id = $(this).data("id2");
var message = $(this).text();
edit_data(id, message, "message");
});
$(document).on('click', '.btn_delete', function(){
var id=$(this).data("id3");
if(confirm("Are you sure you want to delete this?"))
{
$.ajax({
url:"delete.php",
method:"POST",
data:{id:id},
dataType:"text",
success:function(data){
alert(data);
fetch_data();
}
});
}
});
});
</script>
</body>
</html>
Solution 1:[1]
I'm not sure exactly how you are sending the data from the HTML to the PHP page in this example, so I will give you a generic simple implementation and hopefully you can figure out how to work it into your project.
HTML:
Using checkboxes, you can send multiple values as an array to a php script like so.
<form action="delete.php" method="POST">
<input name="delete[]" value="id_of_row" type="checkbox">
<input name="delete[]" value="id_of_another_row" type="checkbox">
<button type="submit">Submit</button>
</form>
This will send an array of whatever is in the value attribute of each box that is checked. You would then be able to delete every row that was checked with the following php script.
PHP:
<?php
$connect = mysqli_connect("localhost", "root", "root", "appointments");
foreach($_POST['delete'] as $id){
$sql = "DELETE FROM appointments WHERE id = '" . $id . "';
if(mysqli_query($connect, $sql))
{
echo 'Data Deleted';
}
}
?>
This should be what you need to somehow implement into your existing project so that you can delete multiple rows at once.
Solution 2:[2]
It will be cleaner and more professional to send an array of ids to your php file as suggested by earlier answers:
<input name="ids[]" value="<?php echo $id; ?>" type="checkbox">
...then make just one trip to the database to delete multiple rows.
if (
empty($_POST['ids'])
// || array_filter($_POST['ids'], function($v) { return !ctype_digit($v); })
) {
exit('Missing/Invalid data submitted'); // be deliberately vague
}
$connect = new mysqli("localhost", "root", "root", "appointments");
$count = count($_POST['ids']);
$stmt = $connect->prepare(
sprintf(
"DELETE FROM appointments WHERE id IN (%s)",
implode(',', array_fill(0, $count, '?')) // e.g if 3 ids, then "?,?,?"
)
);
$stmt->bind_param(str_repeat('i', $count), ...$_POST['ids']);
$stmt->execute();
printf('Deleted %d row(s)', $stmt->affected_rows());
This resembles a similar post of mine: SELECT with dynamic number of values in IN()
Solution 3:[3]
You should be using array for this.
<td><input type="checkbox" name='check[]' value=".$row['id']." /></td>
and in delete function you should be doing something like this.
<?php
$connect = new mysqli("localhost", "root", "root", "appointments");
$totalCheckboxChecked = sizeof($_POST['check']);
for($i=0;$i<$totalCheckboxChecked;$i++)
{
$idToDelete = $check[$i];
$sql = "DELETE FROM appointments WHERE id = $idToDelete";
$result=$connect->query($sql);
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | |
| Solution 3 | Sanzeeb Aryal |