April 30, 2017
AngularJS Sorting, Searching and Pagination of Data Table using PHP & MySQL
Hi, friends in this post I show you how to do AngularJS Sorting, Searching and Pagination of Data Table using PHP & MySQL. AngularJS Data table module helps to create attractive grid listing in your angularJS web application using angularJS directive. The main advantage of AngularJS Data-table is open source and light weighted. Grid view is a really important web part in the modern world. Sorting, searching, pagination is not a simple job in HTML tables.
In primary initialization, data table provides sorting and immediate searching by loading complete data records at once. It can be a display issue fetching a huge amount of data from MySQL server. It will be helpful if you integrate server-side sorting, searching and pagination so we can break huge amount data in the part, So performance will improve extremely.
If you like to export your DataTables in various format here you have an article.
index.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
<!doctype html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width,initial-scale=1"> <title>SoftAOX | AngularJS Sorting, Searching and Pagination of Data Table using PHP & MySQL</title> <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet"> </head> <body> <div ng-app="myApp" ng-controller="controller"> <div class="container"> <br/> <h3 align="center">AngularJS Sorting, Searching and Pagination of Data Table using PHP & MySQL</a></h3> <br/> <div class="row"> <div class="col-sm-2 pull-left"> <label>PageSize:</label> <select ng-model="data_limit" class="form-control"> <option>10</option> <option>20</option> <option>50</option> <option>100</option> </select> </div> <div class="col-sm-6 pull-right"> <label>Search:</label> <input type="text" ng-model="search" ng-change="filter()" placeholder="Search" class="form-control" /> </div> </div> <br/> <div class="row"> <div class="col-md-12" ng-show="filter_data > 0"> <table class="table table-striped table-bordered"> <thead> <th>Name <a ng-click="sort_with('name');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Gender <a ng-click="sort_with('gender');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Age <a ng-click="sort_with('age');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Email <a ng-click="sort_with('email');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Phone <a ng-click="sort_with('phone');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Organization <a ng-click="sort_with('organization');"><i class="glyphicon glyphicon-sort"></i></a></th> </thead> <tbody> <tr ng-repeat="data in searched = (file | filter:search | orderBy : base :reverse) | beginning_data:(current_grid-1)*data_limit | limitTo:data_limit"> <td>{{data.name}}</td> <td>{{data.gender}}</td> <td>{{data.age}}</td> <td>{{data.email}}</td> <td>{{data.phone}}</td> <td>{{data.organization}}</td> </tr> </tbody> </table> </div> <div class="col-md-12" ng-show="filter_data == 0"> <div class="col-md-12"> <h4>No records found..</h4> </div> </div> <div class="col-md-12"> <div class="col-md-6 pull-left"> <h5>Showing {{ searched.length }} of {{ entire_user}} entries</h5> </div> <div class="col-md-6" ng-show="filter_data > 0"> <div pagination="" page="current_grid" on-select-page="page_position(page)" boundary-links="true" total-items="filter_data" items-per-page="data_limit" class="pagination-small pull-right" previous-text="«" next-text="»"></div> </div> </div> </div> </div> </div> <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.2.12/angular.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/angular-ui-bootstrap/0.10.0/ui-bootstrap-tpls.min.js"></script> <script src="myapp.js"></script> </body> </html> |
myapp.js
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
var app = angular.module('myApp', ['ui.bootstrap']); app.filter('beginning_data', function() { return function(input, begin) { if (input) { begin = +begin; return input.slice(begin); } return []; } }); app.controller('controller', function($scope, $http, $timeout) { $http.get('fetch.php').success(function(user_data) { $scope.file = user_data; $scope.current_grid = 1; $scope.data_limit = 10; $scope.filter_data = $scope.file.length; $scope.entire_user = $scope.file.length; }); $scope.page_position = function(page_number) { $scope.current_grid = page_number; }; $scope.filter = function() { $timeout(function() { $scope.filter_data = $scope.searched.length; }, 20); }; $scope.sort_with = function(base) { $scope.base = base; $scope.reverse = !$scope.reverse; }; }); |
fetch.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<?php $conn = new mysqli('localhost', 'root', '', 'angularjs_datatable'); $query = "select distinct name, gender, age, email, phone, organization from user_data order by id"; $result = $conn->query($query) or die($conn->error . __LINE__); $fetch_data = array(); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { $fetch_data[] = $row; } } $jResponse = json_encode($fetch_data); echo $jResponse; ?> |
