Update/Delete Multiple Rows using PHP

We are well expertise with PHP CRUD operations by accessing MySQL via PHP logic. Yet, we have seen about how to update and delete table rows one at a time. This article deals with selecting multiple rows for applying update/delete operations.

For selecting multiple rows, we are going to use checkbox input for submitting selected rows reference. We can get selected checkbox values via jQuery AJAX post or by accessing the form from PHP after page refresh.

Steps in PHP Multiple Rows Update/Delete

While implementing multiple rows update or delete, we need to follow some steps. All the steps listed below is suitable for updating multiple rows. But, step 2, 3 is not required for deleting multiple rows. Instead, we can show a Javascript popup to ask for confirmation of delete.

1. Selecting rows using check box input.
2. Show form UI for updating table columns.
3. Submit array of row details to PHP.
4. Iterate through row details array to apply update/delete query for each.

First, let us start coding for updating multiple rows. And then, we can simplify it for delete operation by removing unwanted steps like a form submits and etc.

Multiple Rows Update using Checkbox

We are going to take similar example seen in PHP CRUD article. From that example, let us take list_user.php and edit_user.php and continue with the list of steps stated above. With these steps, we should make little changes on list_user.php and edit_user.php.

Selecting Rows using Checkboxes.

For each users row, we need to add checkbox input while iterating over a loop with the query result. So, we should create another column with user’s list view for checkbox input.

So, while printing user table entries dynamically to the list view, the loop should contain the following items.

<?php
$i=0;
while($row = mysql_fetch_array($result)) {
if($i%2==0)
$classname="evenRow";
else
$classname="oddRow";
?>
<tr class="<?php if(isset($classname)) echo $classname;?>">
<td><input type="checkbox" name="users[]" value="<?php echo $row["userId"]; ?>" ></td>
<td><?php echo $row["userName"]; ?></td>
<td><?php echo $row["firstName"]; ?></td>
<td><?php echo $row["lastName"]; ?></td>
<td><a href="edit_user.php?userId=<?php echo $row["userId"]; ?>" class="link"><img alt='Edit' title='Edit' src='images/edit.png' width='15px' height='15px' hspace='10' /></a>  <a href="delete_user.php?userId=<?php echo $row["userId"]; ?>"  class="link"><img alt='Delete' title='Delete' src='images/delete.png' width='15px' height='15px'hspace='10' /></a></td>
</tr>
<?php
$i++;
}
?>

The HTML tags will recursively create with dynamic values for each iteration of the loop. Checkbox added to each user’s row will contain their id, accordingly, as its value. And, it will look like,

These values will be passed as an array of user ids to the PHP page. With these selected checkbox values, the form is submitted via Javascript. On clicking Update button, it will trigger setUpdateAction() event handler to set form action attribute before submit. And the script is,

function setUpdateAction() {
document.frmUser.action = "edit_user.php";
document.frmUser.submit();
}

Show Edit Form for Selected Rows.

Previously, we have seen about how to handle edit for each record. So, we showed only one tile containing single user details. But now, we should show multiple tiles, since we are updating multiple users at a time.

For that, we need to make two main changes with the edit_user.php, we have seen in PHP CRUD.

• We should create editable form elements containing user details recursively for each user. This is similar to that we have done with user’s list view. The only difference is that we are displaying them with form input element to create edit interface.
• And then, the second change, we should keep all elements name as an array. For example, if the element name is userName, then, it should be specified as userName[]. We can have more clearance on seeing the code below.

<?php
$rowCount = count($_POST["users"]);
for($i=0;$i<$rowCount;$i++) {
$result = mysql_query("SELECT * FROM users WHERE userId='" . $_POST["users"][$i] . "'");
$row[$i]= mysql_fetch_array($result);
?>
<tr>
<td>
<table border="0" cellpadding="10" cellspacing="0" width="500" align="center" class="tblSaveForm">
<tr>
<td><label>Username</label></td>
<td><input type="hidden" name="userId[]" class="txtField" value="<?php echo $row[$i]['userId']; ?>"><input type="text" name="userName[]" class="txtField" value="<?php echo $row[$i]['userName']; ?>"></td>
</tr>
<tr>
<td><label>Password</label></td>
<td><input type="password" name="password[]" class="txtField" value="<?php echo $row[$i]['password']; ?>"></td>
</tr>
<td><label>First Name</label></td>
<td><input type="text" name="firstName[]" class="txtField" value="<?php echo $row[$i]['firstName']; ?>"></td>
</tr>
<td><label>Last Name</label></td>
<td><input type="text" name="lastName[]" class="txtField" value="<?php echo $row[$i]['lastName']; ?>"></td>
</tr>
</table>
</td>
</tr>
<?php
}
?>

Submitting Array of Updated Values.

We are specifying each input as an array. So, on submitting this multiple user information, an array of user information for each user, will be passed to PHP code.

PHP Multi-Dimensional Array Iteration.

By iterating over this multidimensional array, each user record will be updated by setting current iteration values to MySQL UPDATE query. After successful update, the page will be redirected to list view by using PHP location header. And the PHP code is,

<?php
$conn = mysql_connect("localhost","root","");
mysql_select_db("phppot_examples",$conn);

if(isset($_POST["submit"]) && $_POST["submit"]!="") {
$usersCount = count($_POST["userName"]);
for($i=0;$i<$usersCount;$i++) {
mysql_query("UPDATE users set userName='" . $_POST["userName"][$i] . "', password='" . $_POST["password"][$i] . "', firstName='" . $_POST["firstName"][$i] . "', lastName='" . $_POST["lastName"][$i] . "' WHERE userId='" . $_POST["userId"][$i] . "'");
}
header("Location:list_user.php");
}
?>

Deleting Multiple Rows using PHP

Delete is very simple compared to update. Because we need not show any user interface and it requires only the array of selected row ids. For multi-row delete also we are submitting selected rows by using Javascript. As shown in the following script, we can use Javascript confirm() function, to get confirmation before delete.

function setDeleteAction() {
if(confirm("Are you sure want to delete these rows?")) {
document.frmUser.action = "delete_user.php";
document.frmUser.submit();
}
}

In PHP page, DELETE query is executed with MySQL WHERE clause specifying a condition to match appropriate user id in each iteration. And the PHP code for delete will be as follows.

<?php
$conn = mysql_connect("localhost","root","");
mysql_select_db("phppot_examples",$conn);
$rowCount = count($_POST["users"]);
for($i=0;$i<$rowCount;$i++) {
mysql_query("DELETE FROM users WHERE userId='" . $_POST["users"][$i] . "'");
}
header("Location:list_user.php");
?>

Download PHP Multiple Rows Update/Delete Source Code

This PHP code tutorial was published on September 23, 2013.

↑ Back to Top

• PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • PHP Contact Form
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
    • PHP Responsive Image Gallery using CSS Media Queries
    • PHP MyAdmin
    • How to Import SQL into a Database using PHP
    • How to Backup MySQL Database using PHP
    • Restore MySQL Database using PHP
    • Import CSV File into MySQL using PHP
    • Import Excel File into MySQL Database using PHP
    • PHP Database Backup Client for MySQL
    • MySQL Fetch using PHP
    • Access MySQL from PHP
    • PHP CRUD with MySQL
    • CRUD with MySQLi Prepared Statement using PHP
    • PHP PDO CRUD
    • Object-Oriented CRUD System using PHP PDO and MySQL
    • PHP Input Filtering
    • Filtering MySQL Results by Applying PHP Conditions
    • Sorting MySQL Column using PHP
    • Loading List box Dynamically
    • Update/Delete Multiple Rows using PHP
    • PHP Pagination
    • MySQL BLOB using PHP
    • PHP Search and Pagination using PDO
    • DataTables Server-side Processing using PHP with MySQL
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • Files and Directories
  • PHP Date Time
  • PHP Graphics
  • PHP XML
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• WordPress
• MySQL
• CSS

PHP Freelancer

• How to Become a Full Time Freelance PHP Developer
• Top 10 Skills to be a Successful Freelancer
• Importance of Quality Communication in Freelance
• Freelancer's 5 Point Guide to Excellent Client Satisfaction
• Skills Every PHP Freelancer Must Have

• PHP RESTful Web Service
  under RESTful API
• Highcharts - Compare Data using Column Chart
  under jQuery Tutorials
• AJAX Pagination with PHP
  under PHP AJAX
• Simple PHP Shopping Cart
  under PHP Shopping Cart
• jQuery AJAX Inline CRUD with PHP
  under jQuery AJAX CRUD