This tutorial explains how to create a datagrid with paging, searching and sorting functionality using, PHP, MySQL. When we implement paging the data is divided into number of small pages. Hence the browser has to load less amount of data at one stretch. Which in turn increases the data loading speed.
.Database Table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE IF NOT EXISTS `customers` ( `customerNumber` int(11) NOT NULL, `customerName` varchar(50) NOT NULL, `contactLastName` varchar(50) NOT NULL, `contactFirstName` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `city` varchar(50) NOT NULL, `state` varchar(50) DEFAULT NULL, `postalCode` varchar(15) DEFAULT NULL, `country` varchar(50) NOT NULL, `salesRepEmployeeNumber` int(11) DEFAULT NULL, `creditLimit` double DEFAULT NULL, PRIMARY KEY (`customerNumber`), KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Database configuration settings
“/includes/config.php”
getCustomers.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?php include('../includes/config.php'); $query="select distinct c.customerName, c.addressLine1, c.city, c.state, c.postalCode, c.country, c.creditLimit from customers c order by c.customerNumber"; $result = $mysqli->query($query) or die($mysqli->error.__LINE__); $arr = array(); if($result->num_rows > 0) { while($row = $result->fetch_assoc()) { $arr[] = $row; } } # JSON-encode the response $json_response = json_encode($arr); // # Return the response echo $json_response; ?> |
Java Script Code
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 | var app = angular.module('myApp', ['ui.bootstrap']); app.filter('startFrom', function() { return function(input, start) { if(input) { start = +start; //parse to int return input.slice(start); } return []; } }); app.controller('customersCrtl', function ($scope, $http, $timeout) { $http.get('ajax/getCustomers.php').success(function(data){ $scope.list = data; $scope.currentPage = 1; //current page $scope.entryLimit = 5; //max no of items to display in a page $scope.filteredItems = $scope.list.length; //Initially for no filter $scope.totalItems = $scope.list.length; }); $scope.setPage = function(pageNo) { $scope.currentPage = pageNo; }; $scope.filter = function() { $timeout(function() { $scope.filteredItems = $scope.filtered.length; }, 10); }; $scope.sort_by = function(predicate) { $scope.predicate = predicate; $scope.reverse = !$scope.reverse; }; }); |
index.html Page
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 | <div class="row"> <div class="col-md-2">PageSize: <select data-ng-model="entryLimit" class="form-control"> <option>5</option> <option>10</option> <option>20</option> <option>50</option> <option>100</option> </select> </div> <div class="col-md-3">Filter: <input type="text" ng-model="search" ng-change="filter()" placeholder="Filter" class="form-control" /> </div> <div class="col-md-4"> <h5>Filtered {{ filtered.length }} of {{ totalItems}} total customers</h5> </div> </div> <br/> <div class="row"> <div class="col-md-12" data-ng-show="filteredItems > 0"> <table class="table table-striped table-bordered"> <thead> <th>Customer Name <a ng-click="sort_by('customerName');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Phone <a ng-click="sort_by('phone');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Address <a ng-click="sort_by('addressLine1');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>City <a ng-click="sort_by('city');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>State <a ng-click="sort_by('state');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Postal Code <a ng-click="sort_by('postalCode');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Country <a ng-click="sort_by('country');"><i class="glyphicon glyphicon-sort"></i></a></th> <th>Credit Limit <a ng-click="sort_by('creditLimit');"><i class="glyphicon glyphicon-sort"></i></a></th> </thead> <tbody> <tr ng-repeat="data in filtered = (list | filter:search | orderBy : predicate :reverse) | startFrom:(currentPage-1)*entryLimit | limitTo:entryLimit"> <td>{{data.customerName}}</td> <td>{{data.phone}}</td> <td>{{data.addressLine1}}</td> <td>{{data.city}}</td> <td>{{data.state}}</td> <td>{{data.postalCode}}</td> <td>{{data.country}}</td> <td>{{data.creditLimit}}</td> </tr> </tbody> </table> </div> <div class="col-md-12" data-ng-show="filteredItems == 0"> <div class="col-md-12"> <h4>No customers found</h4> </div> </div> <div class="col-md-12" data-ng-show="filteredItems > 0"> <div data-pagination="" data-page="currentPage" data-on-select-page="setPage(page)" data-boundary-links="true" data-total-items="filteredItems" items-per-page="entryLimit" class="pagination-small" data-previous-text="«" data-next-text="»"></div> </div> </div> |
.If you like FreeWebMentor and you would like to contribute, you can write an article and mail your article to [email protected] Your article will appear on the FreeWebMentor main page and help other developers.
