By: Prem Tiwari | Last Updated: | In: Javascript & Ajax, PHP Tutorial
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> |
. 
Prem Tiwari is the founder of FreeWebMentor.com and also a professional developer who has vast experience in PHP and open source technologies. Apart from this, he is a blogger by hobby and also he has been a regular speaker of WordPress sessions in various IT Companies. View all posts by Prem Tiwari
