Sometimes its require to quickly modify multiple records and save them to the MySQL database table.
It makes easier to update records instead of updating one by one.
With checkboxes, enable multiple records selected from the list.
In this tutorial, I show how you can update multiple selected records to MySQL with PHP.
Contents
1. Table structure
Create users table.
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) DEFAULT NULL, `fname` varchar(50) NOT NULL, `lname` varchar(50) NOT NULL, `age` int(2) DEFAULT NULL, `salary` int(10) NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. Configuration
Create a config.php for database connection.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. Record List
Create a <form > which contains submit button for update selected records and <table > to list records.
In the <table > add a checkbox in a <th> for check/uncheck all checkboxes.
Loop on fetched records and create <tr >. In first <td> add the checkbox for record selection. Set name='update[]' and value='<?= $id ?>'.
jQuery Script –
When checkAll checkbox state change then check if it is checked or not. If checked then set all records checkboxes checked property true otherwise false.
On the update checkbox click check if the total number of checkboxes equals to total checked checkboxes. If equal then set checkAll checkbox checked property true otherwise false.
Completed Code
<div class='container'>
<!-- Form -->
<form method='post' action=''>
<!-- Submit button -->
<input type='submit' value='Update Selected Records' name='but_update'><br><br>
<!-- Record list -->
<table border='1' style='border-collapse: collapse;' >
<tr style='background: whitesmoke;'>
<!-- Check/Uncheck All-->
<th><input type='checkbox' id='checkAll' > Check</th>
<th>Username</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Salary</th>
<th>Email</th>
</tr>
<?php
$query = "SELECT * FROM users";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result) ){
$id = $row['id'];
$username = $row['username'];
$fname = $row['fname'];
$lname = $row['lname'];
$age = $row['age'];
$salary = $row['salary'];
$email = $row['email'];
?>
<tr>
<!-- Checkbox -->
<td><input type='checkbox' name='update[]' value='<?= $id ?>' ></td>
<td><?= $username ?></td>
<td><input type='text' name='fname_<?= $id ?>' value='<?= $fname ?>' ></td>
<td><input type='text' name='lname_<?= $id ?>' value='<?= $lname ?>' ></td>
<td><input type='number' name='age_<?= $id ?>' value='<?= $age ?>' ></td>
<td><input type='number' name='salary_<?= $id ?>' value='<?= $salary ?>' ></td>
<td><input type='text' name='email_<?= $id ?>' value='<?= $email ?>' ></td>
</tr>
<?php
}
?>
</table>
</form>
</div>
<!-- Script -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
// Check/Uncheck ALl
$('#checkAll').change(function(){
if($(this).is(':checked')){
$('input[name="update[]"]').prop('checked',true);
}else{
$('input[name="update[]"]').each(function(){
$(this).prop('checked',false);
});
}
});
// Checkbox click
$('input[name="update[]"]').click(function(){
var total_checkboxes = $('input[name="update[]"]').length;
var total_checkboxes_checked = $('input[name="update[]"]:checked').length;
if(total_checkboxes_checked == total_checkboxes){
$('#checkAll').prop('checked',true);
}else{
$('#checkAll').prop('checked',false);
}
});
});
</script>
4. Update Records
On <form > submit loop on selected checkboxes ids if selected.
Read the values from DOM elements according to $updateid. If all values entered then execute UPDATE query on $updateid.
Completed Code
if(isset($_POST['but_update'])){
if(isset($_POST['update'])){
foreach($_POST['update'] as $updateid){
$fname = $_POST['fname_'.$updateid];
$lname = $_POST['lname_'.$updateid];
$age = $_POST['age_'.$updateid];
$salary = $_POST['salary_'.$updateid];
$email = $_POST['email_'.$updateid];
if($fname !='' && $lname !='' && $age >0 && $salary >0 && $email != '' ){
$updateUser = "UPDATE users SET
fname='".$fname."',lname='".$lname."',
age=".$age.",salary=".$salary.",email='".$email."'
WHERE id=".$updateid;
mysqli_query($con,$updateUser);
}
}
}
}
5. Output
6. Conclusion
For multiple records, selection use checkbox elements and assign record id in the value attribute for accessing it on form submit for select and update records.
You can also use it to delete multiple records.
If you found this tutorial helpful then don't forget to share.