Update/Delete Multiple Rows using PHP

by Vincy. Last modified on July 14th, 2022.

We are well expertise with PHP CRUD operations by accessing MySQL via PHP logic. Yet, we have seen 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 are 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. Select rows using checkbox 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.

PHP MySQL Multiple Update Delete

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 a similar example seen in the 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 to list_user.php and edit_user.php.

Database script

Import this script to have a backend database to run this example. It contains some test records to view the record listing on a landing page.

--
-- Database: `multi_delete_update`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `userId` int(8) NOT NULL,
  `userName` varchar(55) NOT NULL,
  `firstName` varchar(55) NOT NULL,
  `lastName` varchar(55) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`userId`, `userName`, `firstName`, `lastName`) VALUES
(1, 'Liam', 'Noah', 'James'),
(2, 'Emma', 'Amelia', 'Charlotte'),
(3, 'Theodore', 'Lucas', 'James');

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`userId`);

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `userId` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

Listing Database Results with Checkboxes to Allow Bulk Select

For each user’s row, we need to add checkbox input while iterating over a loop with the query result. So, we should create another column with the 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.

index.php

<?php
use Phppot\DataSource;

require_once __DIR__ . "/DataSource.php";
$database = new DataSource();
$sql = "SELECT * FROM users";
$result = $database->select($sql);
?>

This is to rendering HTML table with dynamic user records from the database by using the $result computed above.

<html>
<head>
<title>Users List</title>
<link rel="stylesheet" type="text/css" href="css/style.css" />
<link rel="stylesheet" type="text/css" href="css/table.css" />
<script language="javascript" src="function.js" type="text/javascript"></script>
</head>
<body>
	<div class="phppot-container">
		<h1>Users List</h1>
		<form name="frmUser" method="post" action="">
			<div id="message"><?php if(isset($message)) { echo $message; } ?></div>
			<table class="striped">
				<thead>
					<tr class="listheader">
						<th></th>
						<th>Username</th>
						<th>First Name</th>
						<th>Last Name</th>
					</tr>
				</thead>
<?php
if (is_array($result) || is_object($result)) {
    foreach ($result as $i => $value) {
        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 $result[$i]["userId"]; ?>"></td>
					<td><?php echo $result[$i]["userName"]; ?></td>
					<td><?php echo $result[$i]["firstName"]; ?></td>
					<td><?php echo $result[$i]["lastName"]; ?></td>
				</tr>
<?php
        $i ++;
    }
}
?>
                <tr>
					<td colspan="4"><input type="button" name="update" value="Update"
						onClick="setUpdateAction();" /> <input type="button" name="delete"
						value="Delete" onClick="setDeleteAction();" /></td>
				</tr>
			</table>
		</form>
	</div>
</body>
</html>

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

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 the Update button, it will trigger the setUpdateAction() event handler to set the form action attribute before submitting.

JavaScript OnClick Handlers to Trigger Multiple Update and Delete

The list page button calls these handlers on the click event. It is recommended to choose at least one record to experience a perfect output.

functions.js

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

Show Edit Form for Selected Rows

Previously, we have seen how to handle editing 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, which we have seen in PHP CRUD.

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

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 the PHP code.

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

edit_user.php

<html>
<head>
<title>Edit Multiple User</title>
<link rel="stylesheet" type="text/css" href="css/style.css" />
<link rel="stylesheet" type="text/css" href="css/form.css" />
</head>
<body>
	<div class="phppot-container">
		<form name="frmUser" method="post" action="">
			<div class="message"><?php if(isset($message)) { echo $message; } ?></div>
			<p>
				<a href="index.php" class="font-bold"> List User</a>
			</p>
			<h1>Edit User</h1>
<?php
if (! empty($_POST["users"])) {
    $rowCount = count($_POST["users"]);
    for ($i = 0; $i < $rowCount; $i ++) {
        $sql = "SELECT * FROM users WHERE userId=?";
        $paramType = "i";
        $paramValue = array(
            $_POST["users"][$i]
        );
        $result = $database->select($sql, $paramType, $paramValue);
        ?>
			<div class="row">
				<label>Username<span class="error-color" id="user-name_error"></span>
				</label> <input type="hidden" name="userId[]" class="txtField"
					value="<?php echo $result[0]['userId']; ?>"><input type="text"
					name="userName[]" class="txtField"
					value="<?php echo $result[0]['userName']; ?>">
			</div>
			<div class="row">
				<label>First Name</label> <input type="text" name="firstName[]"
					class="txtField" value="<?php echo $result[0]['firstName']; ?>">
			</div>
			<div class="row">
				<label>Last Name</label> <input type="text" name="lastName[]"
					class="txtField" value="<?php echo $result[0]['lastName']; ?>">
			</div>

			<hr>
<?php
    }
    ?>
<div class="row">
				<input type="submit" name="submit" value="Submit" class="btnSubmit">
				</td>
			</div>
		</form>
	</div>
<?php
}
?>
</body>
</html>

This PHP script checks if the multiple update form is submitted. If so, it iterates the array of user instances and run the update action in a loop.

<?php
use Phppot\DataSource;

require_once __DIR__ . "/DataSource.php";

$database = new DataSource();

if (isset($_POST["submit"]) && $_POST["submit"] != "") {
    $usersCount = count($_POST["userName"]);
    for ($i = 0; $i < $usersCount; $i ++) {
        $query = "UPDATE users set userName=?, firstName=?, lastName=? WHERE userId=?";
        $paramType = "sssi";
        $paramValue = array(
            $_POST["userName"][$i],
            $_POST["firstName"][$i],
            $_POST["lastName"][$i],
            $_POST["userId"][$i]
        );
        $isUpdated = $database->update($query, $paramType, $paramValue);
    }
    header("Location:index.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 deleting.
On the PHP page, the DELETE query is executed with MySQL WHERE clause specifying a condition to match the appropriate user id in each iteration. And the PHP code for delete will be as follows.

delete_user.php

<?php
use Phppot\DataSource;
require_once __DIR__ . "/DataSource.php";

$database = new DataSource();
$rowCount = count($_POST["users"]);
for ($i = 0; $i < $rowCount; $i ++) {
    $query = "DELETE FROM users WHERE userId=?";
    $paramType = "i";
    $paramValue = array(
        $_POST["users"][$i]
    );

    $isDeleted = $database->delete($query, $paramType, $paramValue);
}
header("Location:index.php");
exit();
?>

Download

Vincy
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Comments to “Update/Delete Multiple Rows using PHP”

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Tutorials

↑ Back to Top

Share this page