Filter DataTable with AJAX and PHP

admin

Hello friends, Web Programing. Today there is a way to filter data for you who use the DataTable library and write methods to extract data to display in DataTable using PHP and AJAX. How to go, let’s see.

1. Table structure for collecting data

Create Table ’employee’ in Database

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL, 
  `salary` varchar(20) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Create configuration file

Create config.php file in Database connection

3.Download and Include DataTabel libraries

  • Download Library DataTabel Click
  • Include the DataTabel datatables.min.css and datatables.min.js libraries under the <head> tag, including Include libraries with jQuery.
  • Or you can use Include from the CDN.

4. HTML













   
   

     

       
         
       
       
         
       
     

   


   
   

     
       

         Employee name
         Email
         Gender
         Salary
         City

5. PHP

Create a new ajaxfile.php file.

$row['emp_name'],
     "email"=>$row['email'],
     "gender"=>$row['gender'],
     "salary"=>$row['salary'],
     "city"=>$row['city']
   );
}

## Response
$response = array(
  "draw" => intval($draw),
  "iTotalRecords" => $totalRecords,
  "iTotalDisplayRecords" => $totalRecordwithFilter,
  "aaData" => $data
);

echo json_encode($response);
?>

6.Script

Write the AJAX script to send data to retrieve data to send to Server Side to run. When the data is finished, return data to the Frontend side to display the data in DataTable.

$(document).ready(function(){
  var dataTable = $('#empTable').DataTable({
    'processing': true,
    'serverSide': true,
    'serverMethod': 'post',
    //'searching': false, // Remove default Search Control
    'ajax': {
       'url':'ajaxfile.php',
       'data': function(data){
          // Read values
          var gender = $('#searchByGender').val();
          var name = $('#searchByName').val();

          // Append to data
          data.searchByGender = gender;
          data.searchByName = name;
       }
    },
    'columns': [
       { data: 'emp_name' }, 
       { data: 'email' },
       { data: 'gender' },
       { data: 'salary' },
       { data: 'city' },
    ]
  });

  $('#searchByName').keyup(function(){
    dataTable.draw();
  });

  $('#searchByGender').change(function(){
    dataTable.draw();
  });
});

Demo
Download

85 Views

Related posts