Thursday 13 September 2018

Update Multiple Rows with Checkbox in PHP using Ajax Jquery



Many times we have seen requirement of quickly editing or updating of multiple data into mysql database table. It will process our work fast multiple update of data at the same time instead of editing of data one by one. There are many ways we can update or edit multiple data in a single click of button. But here we have use checkboxes input field, by using checkbox selection we can select multiple rows of data for filter large number of data and tell PHP script particular checkbox selection data only needs for updation. So, With checkboxes we can define multiple rows of data by selection from the list of data.

In this post, We will show you how can we update multiple checkbox selected data to Mysql using PHP with Ajax. If we have use simple PHP script for this task, then it will old style of any web development. Here we have use Ajax which latest style of sending and receiving data from client computer to server without refresh of web page and it has increase the performance of our web application and increase the output of our website. Because updating of multiple data at same time then if we have use simple PHP script then it will take more time in edit of multiple data at the same time. But if we have use Ajax then it will process all backend and it will not display process on web page and after success of process it will display result on web page without refresh of web page. So, here we have use Ajax for update or edit of Multiple Mysql data by using checkbox selection with PHP.

First we want to load or display data on web page, for this we have use Ajax request and make function of fetch data and display on web page in tag at the time of page load. After displaying of all data on web page, we want to make one method for how to convert this table plain text data into editable input fields. For process this task, here we have also use jQuery. By using jQuery functionality when we have checked checkboxes then that selected row of plain text data must be converted into editable HTML input fields with filled value. So when we have select multiple checkboxes then all selected row of data will be converted into editable input field. Now we can edit multiple data for make required changes at the same time and process all data with single click. For this task here we have use Ajax and by using Ajax request we can send multiple data in the form of form data by using jQuery serialize() method and send to PHP script. And in PHP script it will update which every data has been received from Ajax and update multiple data with PHP script.

So, this is whole process in which we have use Ajax, HTML checkboxes, jQuery, PHP and Mysql and make simple and required feature like update of multiple records of Mysql using checkbox selection with Ajax PHP. This is one of the useful method of updating of multiple Mysql table data using checkbox selection with Ajax jquery and PHP. Below you can find complete source code and above you can also find video tutorial in which we have describe step by step process of editing of multiple checkbox selection data using PHP with Ajax.







Source Code


Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_employee`
--

CREATE TABLE `tbl_employee` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` text NOT NULL,
  `gender` varchar(10) NOT NULL,
  `designation` varchar(100) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;






database_connection.php



<?php

//database_connection.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

?>


index.php



<html>  
    <head>  
        <title>Update Multiple Mysql Data using Checkbox with Ajax in PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
          
    </head>  
    <body>  
        <div class="container">  
            <br />
   <div class="table-responsive">  
    <h3 align="center">Update Multiple Mysql Data using Checkbox with Ajax in PHP</h3><br />
    <form method="post" id="update_form">
                    <div align="left">
                        <input type="submit" name="multiple_update" id="multiple_update" class="btn btn-info" value="Multiple Update" />
                    </div>
                    <br />
                    <div class="table-responsive">
                        <table class="table table-bordered table-striped">
                            <thead>
                                <th width="5%"></th>
                                <th width="20%">Name</th>
                                <th width="30%">Address</th>
                                <th width="15%">Gender</th>
                                <th width="20%">Designation</th>
                                <th width="10%">Age</th>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </form>
   </div>  
  </div>
    </body>  
</html>  
<script>  
$(document).ready(function(){  
    
    function fetch_data()
    {
        $.ajax({
            url:"select.php",
            method:"POST",
            dataType:"json",
            success:function(data)
            {
                var html = '';
                for(var count = 0; count < data.length; count++)
                {
                    html += '<tr>';
                    html += '<td><input type="checkbox" id="'+data[count].id+'" data-name="'+data[count].name+'" data-address="'+data[count].address+'" data-gender="'+data[count].gender+'" data-designation="'+data[count].designation+'" data-age="'+data[count].age+'" class="check_box"  /></td>';
                    html += '<td>'+data[count].name+'</td>';
                    html += '<td>'+data[count].address+'</td>';
                    html += '<td>'+data[count].gender+'</td>';
                    html += '<td>'+data[count].designation+'</td>';
                    html += '<td>'+data[count].age+'</td></tr>';
                }
                $('tbody').html(html);
            }
        });
    }

    fetch_data();

    $(document).on('click', '.check_box', function(){
        var html = '';
        if(this.checked)
        {
            html = '<td><input type="checkbox" id="'+$(this).attr('id')+'" data-name="'+$(this).data('name')+'" data-address="'+$(this).data('address')+'" data-gender="'+$(this).data('gender')+'" data-designation="'+$(this).data('designation')+'" data-age="'+$(this).data('age')+'" class="check_box" checked /></td>';
            html += '<td><input type="text" name="name[]" class="form-control" value="'+$(this).data("name")+'" /></td>';
            html += '<td><input type="text" name="address[]" class="form-control" value="'+$(this).data("address")+'" /></td>';
            html += '<td><select name="gender[]" id="gender_'+$(this).attr('id')+'" class="form-control"><option value="Male">Male</option><option value="Female">Female</option></select></td>';
            html += '<td><input type="text" name="designation[]" class="form-control" value="'+$(this).data("designation")+'" /></td>';
            html += '<td><input type="text" name="age[]" class="form-control" value="'+$(this).data("age")+'" /><input type="hidden" name="hidden_id[]" value="'+$(this).attr('id')+'" /></td>';
        }
        else
        {
            html = '<td><input type="checkbox" id="'+$(this).attr('id')+'" data-name="'+$(this).data('name')+'" data-address="'+$(this).data('address')+'" data-gender="'+$(this).data('gender')+'" data-designation="'+$(this).data('designation')+'" data-age="'+$(this).data('age')+'" class="check_box" /></td>';
            html += '<td>'+$(this).data('name')+'</td>';
            html += '<td>'+$(this).data('address')+'</td>';
            html += '<td>'+$(this).data('gender')+'</td>';
            html += '<td>'+$(this).data('designation')+'</td>';
            html += '<td>'+$(this).data('age')+'</td>';            
        }
        $(this).closest('tr').html(html);
        $('#gender_'+$(this).attr('id')+'').val($(this).data('gender'));
    });

    $('#update_form').on('submit', function(event){
        event.preventDefault();
        if($('.check_box:checked').length > 0)
        {
            $.ajax({
                url:"multiple_update.php",
                method:"POST",
                data:$(this).serialize(),
                success:function()
                {
                    alert('Data Updated');
                    fetch_data();
                }
            })
        }
    });

});  
</script>


select.php



<?php

//select.php

include('database_connection.php');

$query = "SELECT * FROM tbl_employee ORDER BY id DESC";

$statement = $connect->prepare($query);

if($statement->execute())
{
 while($row = $statement->fetch(PDO::FETCH_ASSOC))
 {
  $data[] = $row;
 }

 echo json_encode($data);
}

?>


multiple_update.php



<?php

//multiple_update.php

include('database_connection.php');

if(isset($_POST['hidden_id']))
{
 $name = $_POST['name'];
 $address = $_POST['address'];
 $gender = $_POST['gender'];
 $designation = $_POST['designation'];
 $age = $_POST['age'];
 $id = $_POST['hidden_id'];
 for($count = 0; $count < count($id); $count++)
 {
  $data = array(
   ':name'   => $name[$count],
   ':address'  => $address[$count],
   ':gender'  => $gender[$count],
   ':designation' => $designation[$count],
   ':age'   => $age[$count],
   ':id'   => $id[$count]
  );
  $query = "
  UPDATE tbl_employee 
  SET name = :name, address = :address, gender = :gender, designation = :designation, age = :age 
  WHERE id = :id
  ";
  $statement = $connect->prepare($query);
  $statement->execute($data);
 }
}

?>

24 comments:

  1. Great video. I appreciate it.
    How to apply an INSERT to this example?
    That is to say in place of UPDATE, how to INSERT the same data in another table.

    Can you help me make an absence marking system in PHP / AJAX? Thank you

    ReplyDelete
  2. very very nice tutorial
    thanks for this.

    ReplyDelete
  3. hello sir how about using structured? how can i use it using structured programming
    i cant understand too well in this object oriented programming style

    ReplyDelete
  4. How to update multiple row with checkbox using Ajax? in laravel?
    can you help me same work do in laravel

    ReplyDelete
  5. Can we do this without PDO method means core using core Php

    ReplyDelete
  6. Hello, great tutorial. I am having issues because some of my data have double quotes. The data displays fine but when checked and unchecked, the data ends at double quote. Is there a way around this? Any help would be greatly appreciated.

    ReplyDelete
  7. Hi, instead of multiple update, do you have an example where I can select (all/specific only) rows from the table and insert these selected rows to another table? Your help/assistance is much appreciated. - angelojavier2799@gmail.com

    ReplyDelete
  8. please help me to modify the code, i can make it working but i want fetch gender from database too?
    can you show me how to do that with example?

    ReplyDelete
  9. Hi, nice tutorial indeed~!How to write multiple_update.php page if I added one more button for delete and multiple delete?

    ReplyDelete
  10. Hi, nice tutorial indeed~!How to write multiple_update.php page if I added one more button for delete and multiple delete?

    ReplyDelete
  11. Hi, nice tutorial indeed~!How to write multiple_update.php page if I added one more button for delete and multiple delete?

    ReplyDelete
  12. THE CHARACTERS IN ARABS ARE POSTED BY ???????? in the HTML page why who can help me is urgent

    ReplyDelete
  13. how to add code htmlspecialchars into(echo json_encode($data);) in file select.php

    ReplyDelete
  14. When I run it notting is showing on the table

    ReplyDelete
  15. Beautiful tutorial!
    Can you help me do something like this?

    I have a table with 5 fields per record, I would like to allow the update of only 3 fields but without clicking the checkbox to activate the record, directly allow the update via the multi update button.
    Is it possible?

    ReplyDelete
  16. I have Use this coding in select page data display but not display in index page .

    ReplyDelete
  17. Great tutorial. But how can we insert the same data to the database?

    ReplyDelete