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 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 checkbox input.
2. Show form UI for updating table columns.
3. Submit array of row details to PHP.
4. Iterate into 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 form submit 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 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";
?>
php if(isset($classname)) echo $classname;?>">
php echo $row["userId"]; ?>" >

php echo $row["userName"]; ?> php echo $row["firstName"]; ?> php echo $row["lastName"]; ?> php echo $row["userId"]; ?>" class="link"> alt='Edit' title='Edit' src='images/edit.png' width='15px' height='15px' hspace='10' /> php echo $row["userId"]; ?>" class="link"> alt='Delete' title='Delete' src='images/delete.png' width='15px' height='15px'hspace='10' /> php $i++; } ?>

The HTML tags will recursively created with dynamic values for each iteration of the loop. Checkbox added for 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 PHP page. With these selected checkbox values, the form is submitted via Javascript. On clickinh 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);
?>




 border="0" cellpadding="10" cellspacing="0" width="500" align="center" class="tblSaveForm">


Username

php echo $row[$i]['userId']; ?>">php echo $row[$i]['userName']; ?>"> Password php echo $row[$i]['password']; ?>"> First Name php echo $row[$i]['firstName']; ?>"> Last Name php echo $row[$i]['lastName']; ?>"> php } ?>

Submitting Array of Updated Values.

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

PHP Multi Dimensional Array Iteration.

By iterating over this multi dimensional 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 with 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 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 MySQL tutorial was added on September 23, 2013.