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);
 }
}

?>

0 comments:

Post a Comment