datagrid paging, sorting, filter using PHP and MySQL

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.

datagrid

Database Table

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

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

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

<div class="col-md-2">PageSize:

</select>

</div>

<div class="col-md-3">Filter:

</div>

<h5>Filtered {{ filtered.length }} of {{ totalItems}} total customers</h5>

</div>

<br/>

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

<td>{{data.customerName}}</td>

<td>{{data.phone}}</td>

<td>{{data.addressLine1}}</td>

<td>{{data.state}}</td>

<td>{{data.postalCode}}</td>

<td>{{data.country}}</td>

<td>{{data.creditLimit}}</td>

</tr>

</tbody>

</table>

</div>

<h4>No customers found</h4>

</div>

</div>

datagrid paging, sorting, filter using PHP and MySQL

Recommended Posts:

Prem Tiwari

Leave a Reply Cancel reply

Solved Programs