Angularjs datagrid paging, sorting, filter using PHP and Mysql

This tutorial explains how to create a datagrid similar to ASP.NET gridview with client side paging, searching and sorting functionality using AngularJS, PHP, MySQL. Following are links to the demo and download of the final application.
I have used angular-ui directive for the user interface of paging and some basic functionality.
While working with angularjs I found out the biggest issue using angular is it makes the Browser DOM slow while displaying huge amount of data. Hence it becomes necessary to load data partially through paging or infinite scrolling.
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 setup:
Download the source code and import customers.sql to your MySQL database.
I’ve structured the entire application into 5 different folders
CustomersTable
This contains all the information about customers which we intend to display in our grid.
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, | |
`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; |
/includes/db.php
– Contains database configuration settings
<?php | |
$DB_HOST = '127.0.0.1'; | |
$DB_USER = 'root'; | |
$DB_PASS = ''; | |
$DB_NAME = 'angularcode_grid'; | |
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME); | |
?> |
/ajax/getCustomers.php
– Contains php files which needs server side communication
- Connect to MySQL database
- Retrieve all the customers and convert into JSON format
<?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; | |
?> |
/app/app.js
– AngularJs application codes
- Get all the customers returned by the ajax/getCustomers.php and store it in our applications scope
- Implement paging, sorting and filter functionalities
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; | |
}; | |
}); |
/css/bootstrap.css
– Adds bootstrap styles to our application
/js
– Contains supporting javascript library files [angular.min.js, ui-bootstrap-tpls-0.10.0.min.js]
index.html
The index.html file which contains the datagrid
<!DOCTYPE html> | |
<html ng-app="myApp" ng-app lang="en"> | |
<head> | |
<meta charset="utf-8"> | |
<link href="css/bootstrap.min.css" rel="stylesheet"> | |
<style type="text/css"> | |
ul>li, a{cursor: pointer;} | |
</style> | |
<title>Simple Datagrid with search, sort and paging using AngularJS, PHP, MySQL</title> | |
</head> | |
<body> | |
<div class="navbar navbar-default" id="navbar"> | |
<div class="container" id="navbar-container"> | |
<div class="navbar-header"> | |
<a href="http://angularcode.com" class="navbar-brand"> | |
<small> | |
<i class="glyphicon glyphicon-log-out"></i> | |
AngularCode / AngularJS Demos | |
</small> | |
</a><!-- /.brand --> | |
</div><!-- /.navbar-header --> | |
<div class="navbar-header pull-right" role="navigation"> | |
<a href="http://angularcode.com/angularjs-datagrid-paging-sorting-filter-using-php-and-mysql/" class="btn btn-sm btn-danger nav-button-margin"> <i class="glyphicon glyphicon-book"></i> Tutorial Link</a> | |
<a href="http://angularcode.com/download-link/?url=https://app.box.com/s/kyomkfyeb42irie6rxcl" class="btn btn-sm btn-warning nav-button-margin"> <i class="glyphicon glyphicon-download"></i> Download Project</a> | |
</div> | |
</div> | |
</div> | |
<div ng-controller="customersCrtl"> | |
<div class="container"> | |
<br/> | |
<blockquote><h4><a href="http://angularcode.com/angularjs-datagrid-paging-sorting-filter-using-php-and-mysql/">Simple Datagrid with search, sort and paging using AngularJS + PHP + MySQL</a></h4></blockquote> | |
<br/> | |
<div class="row"> | |
<div class="col-md-2">PageSize: | |
<select 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" 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>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.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" ng-show="filteredItems == 0"> | |
<div class="col-md-12"> | |
<h4>No customers found</h4> | |
</div> | |
</div> | |
<div class="col-md-12" ng-show="filteredItems > 0"> | |
<div pagination="" page="currentPage" on-select-page="setPage(page)" boundary-links="true" total-items="filteredItems" items-per-page="entryLimit" class="pagination-small" previous-text="«" next-text="»"></div> | |
</div> | |
</div> | |
</div> | |
</div> | |
<script src="js/angular.min.js"></script> | |
<script src="js/ui-bootstrap-tpls-0.10.0.min.js"></script> | |
<script src="app/app.js"></script> | |
</body> | |
</html> |
I hope the above tutorial will get you started handling large MySQL data tables using AngularJS and PHP. If you have any improvement ideas or suggestions please add comments bellow.

Most Popular Posts
- ShopNx - The assistant manager for influencers
- Frontendfun marketplace for software projects
- Complete steps to configure elasticsearch on Ubuntu
- Configure Vultr for Nodejs Deployment
- Appointment Booking Microservice using Javascript Fullstack
- 100+ most effective ways to promote a new blog for free
- Steps to Configure Digital Ocean Droplet for Nodejs Application Deployment
- Appointment Booking using Angularjs, Nodejs, Mongodb
- Send email with PDF attachment using PHP
- Simple task manager application using Angularjs PHP Mysql
- Steps to Configure Amazon EC2 for Nodejs app deployment
- Inventory Manager Using Angularjs Mysql Php
- User authentication using Angularjs, PHP, Mysql
- Demo of a simple CRUD Restful php service used with Angularjs and Mysql
- Simple file upload example using Angularjs
- Generate PDF using PHP from Mysql database
- Creating REST API using Nodejs and consuming in Angularjs
- Simple project demonstrates how to send email using Nodejs
- Voting system similar to stackoverflow using Angularjs PHP and Mysql
- Angularjs datagrid paging, sorting, filter using PHP and Mysql
- Useful database helper class to generate CRUD statements using PHP and Mysql
- Online Shopping Mega Menu using Angularjs, PHP, Mysql
- How to create a facebook style autocomplete using Angularjs
- Steps configuring PHP Cron Jobs - Godaddy
- How to change Mysql password
- A simple Angularjs web app that converts text to url format
- Creating SWAP file on Linux