Table with Search and Sort using AngularJS and PHP

Search filter makes easier to filter the list of records and view the only required records. This saves time when there is the huge number of records are available on the list.

There is already an orderBy filter is available on AngularJS which will be used for sorting the list.

In this tutorial, I am filtering MySQL table records while fetching data using AngularJS and PHP.

Table with search and sort using AngularJS and PHP


Contents

  1. Table structure
  2. Configuration
  3. HTML
  4. PHP
  5. Script
  6. Demo
  7. Conclusion

 


 

1. Table structure

Create employees table.

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

 

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. HTML

Create a textbox for search value where define ng-model='searchText' and ng-keyup='fetchEmployees()' directives. The model is used to read value and call fetchEmployees() method when keyup event triggered.

Add ng-click='sortColumn()' directive on <table> header column where pass the name of the field.

Using ng-repeat directive to display values from employees variable and for sorting the list added an orderBy filter which takes the name of the column and sort order (asc or desc).

The values of the variable defined in orderBy will change according to the header click from the script.

Completed Code

<body ng-app='myapp'>

 <div ng-controller="fetchCtrl">
 
   <!-- Search Element -->
   <input type='text' ng-keyup='fetchEmployees()' ng-model='searchText' placeholder='Enter search text'><br><br>

   <!-- Table -->
   <table border='1'>
    <tr >
     <th ng-click='sortColumn("emp_name")' >Name</th>
     <th ng-click='sortColumn("salary")' >Salary</th>
     <th ng-click='sortColumn("gender")' >Gender</th>
     <th ng-click='sortColumn("city")' >City</th>
     <th ng-click='sortColumn("email")' >Email</th>
    </tr>
    <tr ng-repeat='employee in employees|orderBy:column:reverse'>
     <td width='20%' align='center'>{{employee.emp_name}}</td>
     <td width='35%' align='center'>{{employee.salary}}</td>
     <td width='20%' align='center'>{{employee.gender}}</td>
     <td width='25%' align='center'>{{employee.city}}</td>
     <td width='25%' align='center'>{{employee.email}}</td>
    </tr>
   </table>
 
 </div>
 
</body>

 

4. PHP

Create a new ajaxfile.php file.

From the script pass a boolean value in sortOrder. If its true then set $sortBy = "desc" otherwise $sortBy = "asc".

Create a select query where use the $search value in where clause if it is not empty.

Use the $sortColumn and $sortBy in order by clause.

Fetch all records and initialized $data.

Return $data as JSON format.

Completed Code

<?php

include 'config.php';

$data = json_decode(file_get_contents("php://input"));

## Values
$search = $data->searchText; // Search value
$sortColumn = $data->sortColumn; // Sort Column name
$sortOrder = $data->sortOrder; // Boolean value

$sortBy = "asc";
if($sortOrder){
 $sortBy = "asc";
}

## Select query
$select_emp = "select * from employees where 1 ";

if($search != ''){
 $select_emp .= " and (emp_name like '%".$search."%' OR 
 salary like '%".$search."%' OR
 gender like '%".$search."%' OR
 city like '%".$search."%' OR
 email like '%".$search."%')";
}

$select_emp .= " order by ".$sortColumn." ".$sortBy;

## Fetch records
$fetchRecords = mysqli_query($con,$select_emp);
$data = array();

while ($row = mysqli_fetch_array($fetchRecords)) {
 $data[] = array("emp_name" => $row['emp_name'],
 "salary" => $row['salary'],
 "gender" => $row['gender'],
 "city" => $row['city'],
 "email" => $row['email']
 );
}

echo json_encode($data);

 

5. Script

Define $scope.column='emp_name' and $scope.reverse=false. The column variable contains the name of the default field by which table sort and the reverse variable store boolean value.

Set it false for ascending. If you want descending order then set it true.

Also, define two methods sortColumn and fetchEmployees.

  • sortColumn – This been called in table header click. Assign passed argument in $scope.column. If $scope.reverse value is true then set it false otherwise true.

Call $scope.fetchEmployees() method to get records.

  • fetchEmployees – Read the input value from the search input box using $scope.searchText. If it is undefined then set searchText = ''.

Send $http service request to fetch records where pass search value, sort column, and sort order values as data.

On successfully callback assign response.data in $scope.employees.

For loading the records on page load call $scope.fetchEmployees() method.

Completed Code

var fetch = angular.module('myapp', []);

fetch.controller('fetchCtrl', ['$scope', '$http', function ($scope, $http) {
 
  // column to sort
  $scope.column = 'emp_name';
 
  // sort ordering (Ascending or Descending). Set true for descending
  $scope.reverse = false;

  // called on header click
  $scope.sortColumn = function(col){
     $scope.column = col;
     if($scope.reverse){
        $scope.reverse = false;
     }else{
        $scope.reverse = true; 
     }
     $scope.fetchEmployees();
  };

  // Fetch data
  $scope.fetchEmployees = function(){
 
    var searchText = $scope.searchText;
    if(searchText == undefined){
      searchText = '';
    }

    $http({
      method: 'post',
      url: 'getData.php',
      data: {searchText:searchText,sortColumn:$scope.column,sortOrder:$scope.reverse}
    }).then(function successCallback(response) {
      $scope.employees = response.data;
    });
  }

  $scope.fetchEmployees();
}]);

 

6. Demo

Click on the table header column name to sort and enter a value in the search box to filter list. Open in a new tab.


 

7. Conclusion

If you are displaying static records using JSON on the page then you can directly use filter where pass the search expression and does not need to define ng-key directive.

Related Post

Spread the love

Be First to Comment

Leave a Reply

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