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 Update Delete Multiple Rows using PHP.
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 Update Delete Multiple Rows using PHP
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.
- Selecting rows using checkbox input.
- Show form UI for updating table columns.
- Submit array of row details to PHP.
- 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.
Listing Database Result with Checkboxes to Allow Bulk Select
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
require_once "db.php";
$result = mysqli_query($conn, "SELECT * FROM users");
?>
<html>
<head>
<title>Users List</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
<script language="javascript" src="users.js" type="text/javascript"></script>
</head>
<body>
<form name="frmUser" method="post" action="">
<div style="width:500px;">
<table border="0" cellpadding="10" cellspacing="1" width="500" class="tblListForm">
<tr class="listheader">
<td></td>
<td>Username</td>
<td>First Name</td>
<td>Last Name</td>
</tr>
<?php
$i=0;
while($row = mysqli_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>
</tr>
<?php
$i++;
}
?>
<tr class="listheader">
<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 with 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 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.
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.
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.
<?php
require_once "db.php";
if(isset($_POST["submit"]) && $_POST["submit"]!="") {
$usersCount = count($_POST["userName"]);
for($i=0;$i<$usersCount;$i++) {
mysqli_query($conn, "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:index.php");
}
?>
<html>
<head>
<title>Edit Multiple User</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<form name="frmUser" method="post" action="">
<div style="width:500px;">
<table border="0" cellpadding="10" cellspacing="0" width="500" align="center">
<tr class="tableheader">
<td>Edit User</td>
</tr>
<?php
$rowCount = count($_POST["users"]);
for($i=0;$i<$rowCount;$i++) {
$result = mysqli_query($conn, "SELECT * FROM users WHERE userId='" . $_POST["users"][$i] . "'");
$row[$i]= mysqli_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
}
?>
<tr>
<td colspan="2"><input type="submit" name="submit" value="Submit" class="btnSubmit"></td>
</tr>
</table>
</div>
</form>
</body></html>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
require_once "db.php";
$rowCount = count($_POST["users"]);
for($i=0;$i<$rowCount;$i++) {
mysqli_query($conn, "DELETE FROM users WHERE userId='" . $_POST["users"][$i] . "'");
}
header("Location:index.php");
?>