Update Multiple Selected Records with PHP

Sometimes its require to quickly modify multiple records and save it to the MySQL database table.

It makes easier to update records instead of updating one by one.

With checkboxes, enable multiple records selection from the list.

In this tutorial, I show how you can update multiple selected records to MySQL with PHP.

Update multiple selected records with PHP


Contents

  1. Table structure
  2. Configuration
  3. Record List
  4. Update Records
  5. Output
  6. Conclusion

 


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.

Related Post

Spread the love
  • 1
  •  
  •  
  •  
  •  
  •  

Be First to Comment

Leave a Reply

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