Delete Multiple Rows Using PHP & MySQL

Delete Multiple Records with Checkbox in PHP & MySQL

Hi! This time I have come up with a php tutorial that demonstrates about deleting multiple records with checkbox using PHP & MySQL database. Under CRUD management, we should provide option for deleting multiple rows from database as deleting records one by one would be time consuming. Also it is recommended to ask for user confirmation before deleting records to avoid accidental clicks. Here we’ll see how to implement this multiple delete in php and mysql.

Delete Multiple Records in PHP MySQL using Checkbox:

As for the demo goes, we have to create a user interface with an html table displaying all user records from the database along with a checkbox on each row. The user has to tick the checkbox to pick on the rows to delete and click on the ‘Delete’ button provided underneath the table.

Just to improve user experience we also have to give an option for the user to select all checkboxes at once by checking on the checkbox placed on the header column of the table. Then we have to show a popup to confirm user action and once user acknowledges it, we have to delete all the selected records and refresh the users table and show some sort of notification.

Create Database:

Here is the mysql database we’d need for the demo.

CREATE DATABASE `db_demo`; Use `db_demo`; CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(30) NOT NULL, `lname` varchar(30) NOT NULL, `email` varchar(60) NOT NULL, `city` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6; INSERT INTO `users` (`id`, `fname`, `lname`, `email`, `city`) VALUES (1, 'Jim', 'Connor', 'jimconnor@yahoo.com', 'Las Vegas'), (2, 'Taylor', 'Fox', 'taylorfox@hotmail.com', 'San Francisco'), (3, 'Daniel', 'Greyson', 'danielgreyson@hotmail.com', 'New York'), (4, 'Julia', 'Brown', 'juliabrown@gmail.com', 'Los Angeles'), (5, 'Rose', 'Harris', 'roseharris@gmail.com', 'New York');

HTML & PHP Code:

Here is the index.php file which contains the user interface we have discussed earlier and php code to delete multiple records from database.

index.php

 ?>       
 
 
 
   ?> 
 
 
 
First Name
 
Last Name
 
Email ID
 
City
 
 
 
  
"/>
 
 
 
 ?>
 
 
 
 
 
     

We have used two jquery functions in our code, the first one $(‘#chk_all’).click() — which is triggered when the user check/uncheck the header column checkbox chk-all . It selects/deselects all the checkboxes based up the checked status of ‘chk_all’ checkbox.

And the next jquery function $(‘#delete_form’).submit() will be fired when the user click on the #submit button which pops up a confirmation alert and submits the form only when user clicks OK button.

Once the records are deleted the page will be refreshed with the remaining set of user records and a success notification will be displayed on the page.

This demo uses procedural approach but if you want to do it with PHP PDO, then check for this tutorial.

That explains about how to delete multiple records with checkbox in php and mysql database. You can customize the code to suit your need. Meet you in another interesting tutorial.

Источник

Deleting Multiple Records using Checkboxes in PHP

I am having an issue where I need to be able to delete multiple records using checkboxes. Here is the code that I currently have.

    
 
           Date:
 
Ticket #:
 
Description:
 
 
      Result :
 
 
 
   
 
  \n"; $cols = 0; while ($get_info = mysql_fetch_assoc($result))< $id = $get_info->id; if($cols == 0) < $cols = 1; print ""; print "Select"; foreach($get_info as $col => $value) < print "$col"; > print "\n"; > print "\n"; print " "; foreach ($get_info as $field) print "\t$field\n"; print "\n"; > print "\n"; mysql_close(); ?> Delete.php > ?> 

I just want to be able to delete rows checked. How would I go about doing this effectively and efficiently? Thank you in advance.

something like DELETE FROM tablename WHERE Auto IN (‘.implode(‘,’,array_map(‘intval’,$_POST[‘selected’])).’)’

You should be cautious with this method of directly using the $_POST values. The query you posted is susceptible to XSS attacks. Wrikken has the gist of it though, you’ll want to use implode.

Источник

Как удалить отмеченные чекбоксом?

На PHP немного псевдокод сделал, но смыл того что есть показывает. Сейчас главное как-то по нажатию кнопки сделать массив, после этого буду думать как дальше передать в запрос на удаление.

$delete = array(); for ($i = 0; $i < $Num; $i++) <  $item  > if(isset($_POST['delete'])) < // Эмм. как массив-то из input собрать для удаления? >

Про «кавычки вокруг name в чекбоксах» это так? name=»delete[]» Ну убрал из сабмита name=»delete» и как мне теперь isset($_POST[‘delete’]) проверять?

echo "
"; echo ""; for ($i = 0; $i < count($items); $i++) < $id = $items[$i]['id']; $text = $items[$i]['text']; echo " \n"; > echo "
 Удалить
 
$text
 
"; echo ""; echo "
";

Все отмеченные id, и только отмеченные, появятся в массиве $_POST[‘delete’].
С ними нужно уже делать то, что вам там надо — например, передать в SQL запрос DELETE.
Только помните, что в массиве могут прийти не только числа, хакер может передать туда строчки с sql-инъекцией.

Да, я уж попробовал цикл в форме как выше писали, работает. Я через PDO с prepare работаю, там вроде защита от инъекций встроенная. Да и инъекции по-моему опасны всё же только при INSERT?

Источник

Delete Multiple Records with Checkboxes in PHP & MySQL

In this tutorial, You will learn to delete multiple records with checkboxes in PHP and MYSQL step by step. Even You will get a complete source code that will be very easy to integrate into your website.

Generally, We need to delete display the largest records of data in the HTML table. It is very inconvenient to delete all or multiple records one by one and It also takes more must time. So, You need to implement a bulk delete feature in your project.

In the case of Bulk delete functionality, you can easily delete all or multiple records from the MySQL Database at once by selecting using checkboxes.

Delete Multiple Rows From MySQL Database with PHP

Now, Let’s start coding to delete multiple rows from the MySQL database from the next steps. But getting started, You should have already stored records in the database. Otherwise, first of all, you will have to insert data into the database using PHP & MYSQL then start coding for deleting data from the database.

myproject/ |__backend.php |__custom.js |__ rocords-table.html

Learn Also –

Let’s start its coding from the next steps –

1. Connect MySQL Database

First of all, connect the MYSQL database to PHP using the following SQL query in PHP

connect_error) < die("Connection failed: " . $conn->connect_error); > ?>

2. Fetch and delete multiple records from the database

To fetch and delete multiple records from the database, you will have to implement the following points –

• First of all, You should include database.php to connect the database
• check checkedId (checkbox name of multiple records) and deleteAll (checkbox name of the select all ) are set or not using $_POST. If both are set then call the deleteMultipleData()

fetch_data() – This function accept a single parameters $conn returns all records from that database.

deleteMultipleData() – This function will execute when you check records & click the delete all button and then It will delete the checked records from the database. It mainly accepts two parameters $conn and $checkedId.

 $fetchData = fetch_data($conn); function fetch_data($conn)< $query = "SELECT id, fullName, gender, email, city FROM developers ORDER BY id DESC"; $result = $conn->query($query); if ($result->num_rows > 0) < $row= mysqli_fetch_all($result, MYSQLI_ASSOC); $data= $row; >else < $data= []; >return $data; > function deleteMultipleData($conn, $checkedId)< $checkedIdGroup = implode(',', $checkedId); $query = "DELETE FROM developers WHERE id IN ($checkedIdGroup)"; $result = $conn->query($query); if($result==true) < return "Selected data was deleted successfully"; >> ?>

3. Display Multiple records with checkboxes

To display multiple records with checkboxes, you will have to implement the following steps –

• You have to include backend.php that contains code to delete & fetch data
• You must include the following jquery CDN to execute the jquery code

File Name – records-table.php

         
 
 
Delete Multiple Records with Checkbox in PHP
 
 
  0) < foreach($fetchData as $data)< ?>  0) < ?>  ?>
S.N
 
Full Name
 
Gender
 
Email
 
City
 
 
">
 
 
 
>else < ?>  
 ?>
  
Check All
 
  
 
 
 
  

4. Select Multiple records with checkboxes

To select multiple records, you will have to implement the following points –

• Apply the submit event on id #deleteForm and call confirmDeleteData() function for confirmation to delete data.
• Also, create checkUncheck() function and implement the follwing steps within it and called it within $(document).ready().
• Assign an id “#singleCheckbox” to a variable #singleCheckbox
• Also, assign “.checkbox-group input[type=’checkbox’]” to another variable checkboxGroup
• Apply click event on singleCheckbox with the following condition
  • If singleCheckbox is checked then all checkboxes will be checked otherwise all checkboxes will be unchecked

  $(document).ready(function()< checkUncheck(); $("#deleteForm").on("submit", function(event)< confirmDeleteData(event); >); >); function checkUncheck()< const boxsingleCheck = '#singleCheckbox'; const checkboxGroup = ".checkbox-group input[type='checkbox']"; if($(document).find(boxsingleCheck).length!==0 || $(document).find(checkboxGroup).length!==0)< $(singleCheckbox).on('click',function()< if(this.checked)< $(checkboxGroup).each(function()< this.checked = true; >); >else< $(checkboxGroup).each(function()< this.checked = false; >); > >); $(checkboxGroup).on('click',function()< if($(checkboxGroup+':checked').length == $(checkboxGroup).length)< $(boxsingleCheck).prop('checked',true); >else < $(boxsingleCheck).prop('checked',false); >>); > > function confirmDeleteData(event) < if($(".checkbox-group input[type='checkbox']:checked").length >0) < var conformation = confirm("Are you sure to delete selected data?"); if(conformation==false)< event.preventDefault(); >>else < alert('Check at least 1 record to delete.'); return false; >>

  Источник

  PHP Checkbox Multiple Delete

  My implementation doesn’t seem to work. Can you point out what might be the problem or point me to a better solution? When I check the checkboxes and click the delete button, it doesn’t seem to do anything. please help me.

   
      
  Post ID No.' . $idNo . '
  ' . $post . ' at ' . $time . ' seconds mark
  '; > if($_SERVER['REQUEST_METHOD'] == "POST" && $_POST['btnPost']) < $postColors = rand(1, 4); $toPost = $_POST['textPost']; $date = date("y-m-d"); $postTime = $_POST['display']; $postTime = floor($postTime); $insertPostQuery = "INSERT INTO tblTimePosts VALUES('','Mimagazine Asia','Chelsea','$postTime','$toPost','$date')"; $query3 = "SELECT PostID FROM tblTimePosts"; $result = mysqli_query($cxn, $insertPostQuery) or die(mysqli_error()); $result3 = mysqli_query($cxn, $query3) or die(mysqli_error()); if($result >0) < while($row = mysqli_fetch_assoc($result3)) < $idNo2 = $row['PostID']; >echo '
  Post ID No.' . $idNo2 . '
  ' . $toPost . ' at ' . $postTime . ' seconds mark
  '; > else echo "Add Failed"; > /* if($_POST['chkDelete']) < for($i=0;$i<5;$i++)< $del_id = $checkDelete[$i]; $sql = "DELETE FROM $tblTimePosts WHERE PostID='$del_id'"; $resulta = mysqli_query($cxn,$sql); >> */ if($_POST['btnDelete']) < // from button name="delete" /* $checkbox = $_POST['checkbox']; //from name="checkbox[]" $countCheck = count($_POST['checkbox']); echo $countCheck; for($i=0;$i<$countCheck;$i++)< $del_id = $checkbox[$i]; $sql = "DELETE FROM tblTimePosts WHERE PostID=".$del_id.""; $resulta = mysqli_query($cxn,$sql) or die(mysqli_error()); >*/ $tbl_name = 'tblTimePosts'; foreach($_POST['checkbox'] as $id => $value) < $sql = 'DELETE FROM `' . $tbl_name . '` WHERE `PostID`=' . (int) $id; mysqli_query($cxn, $sql); >header('Location: videoJudge.php'); > ?>