Angularjs datagrid paging, sorting, filter using PHP and Mysql

Posted 25. July 2019. 7 min read.

Facebook

Twitter

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.

Datagrid using AngularJSDownload this project form Github

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;

view raw angularcode-datagrid-customers.sql hosted with ❤ by GitHub

/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);
      
        ?>

view raw angularcode-datagrid-config.php hosted with ❤ by GitHub

/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;
      
        ?>

view raw angularcode-datagrid-getCustomers.php hosted with ❤ by GitHub

/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;
      
            };
      
        });

view raw angularcode-datagrid-app.js hosted with ❤ by GitHub

/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>&nbsp;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>&nbsp;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&nbsp;<a ng-click="sort_by('customerName');"><i class="glyphicon glyphicon-sort"></i></a></th>
      
                    <th>Address&nbsp;<a ng-click="sort_by('addressLine1');"><i class="glyphicon glyphicon-sort"></i></a></th>
      
                    <th>City&nbsp;<a ng-click="sort_by('city');"><i class="glyphicon glyphicon-sort"></i></a></th>
      
                    <th>State&nbsp;<a ng-click="sort_by('state');"><i class="glyphicon glyphicon-sort"></i></a></th>
      
                    <th>Postal Code&nbsp;<a ng-click="sort_by('postalCode');"><i class="glyphicon glyphicon-sort"></i></a></th>
      
                    <th>Country&nbsp;<a ng-click="sort_by('country');"><i class="glyphicon glyphicon-sort"></i></a></th>
      
                    <th>Credit Limit&nbsp;<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="&laquo;" next-text="&raquo;"></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>

view raw angularcode-datagrid-index.html hosted with ❤ by GitHub

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.

Subscribe my updates via Email

Most Popular Posts

Latest web technology tutorials with working examples.

Follow on Twitter GitHub Facebook

	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;

	<?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);
	?>

	<?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;
	?>

	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;
	};
	});

	<!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>