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


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 ajaxfile.php file.

If $sortColumn has true value then set $sortBy = "desc" otherwise $sortBy = "asc".

Prepare a select query.

If $search is not empty then use $search value to search on the emp_name, salary, gender, city, and email field.

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 = mysqli_real_escape_string($con,$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. 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 Posts