
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.
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.
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.
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.
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(); }
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 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>
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"); ?>
i am getting error in edit_user.php
that AFTER PRESSING UPDATE THEN SUBMIT.. AFTER SUBMIT BUTTON.. NO LINK TO HOME PAGE
Hi Amruta,
What error did you get with edit_user.php?
Excellent Script…..
Thank you Shantanu.
hi, I tried to implement this code but for me it is not fetching the value of the rowcount.Don’t know what to do.
Hi,
Your code is good but may I ask if you have sample also with ‘Add’ entry into database?
hi I using dreamweaver program to implemanted my project with dynamic update recordset have id error
good
Dear madam,
can u suggest me best tool to create php code.
its very useful, thank you
Thanks Man. This really helped. Adorei tudo. obrigado! :) Allah Bless you more.