Pagination Searching and Sorting of data table using Angularjs PHP MySQL

April 27, 2018 | Category : Angular MySQL Ajax Bootstrap PHP

Hi Guys,

In this tutorial, you will learn how to build search sort and pagination of data table in Angularjs app using php mysql. i will help to make simple app with dynamic data get from database and do it searching table data, sorting table fields and pagination table data. here we will use dirPagination.js for pagination.

In this tutorial, we require to create index file and write code for layout using bootstrap and also write code for angularjs application. then we will create one php api file and write code for getting all data from database table and return as json data. now i use code php, you can also use laravel, codeigniter etc php framework.

So, basically we are going to create very small and quick example for pagination, searching and soring of data table like datatable. i also provide demo link so you can see how it looks and what we are building now from this tutorial. So let's follow bellow basic step to create.

Step 1: Create Database table

Here in this step, we need to create database with "tags" table. so i am going to give you sql query for table create as listed below:

Tags table:

CREATE TABLE IF NOT EXISTS `tags` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

`meta_keyword` text COLLATE utf8_unicode_ci NOT NULL,

`meta_description` text COLLATE utf8_unicode_ci NOT NULL,

`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=537

Step 2: Create index.php file

Here we need to create index.php file add bellow code. i also added dirPagination.js file, so you can download from here: dirPagination.js.

index.php

<!doctype html>

<html>

<head>

<title>PHP MySQL - AngularJS Sorting, Searching and Pagination using DataTable - HDTuto.com</title>

<meta charset="utf-8">

<meta name="viewport" content="width=device-width,initial-scale=1">

<link href="https://stackpath.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">

</head>

<body>

<div ng-app="HDTutoApp" ng-controller="controller">

<div class="container">

<hr/>

<h2 align="center">PHP MySQL - AngularJS Sorting, Searching and Pagination using DataTable - HDTuto.com</a></h2>

<hr/>

<div class="row">

<div class="col-md-6 pull-right">

<label>Search:</label>

<input type="text" ng-model="search" ng-change="filter()" placeholder="Please Search" class="form-control" />

</div>

</div>

<hr/>

<!-- All list of client data list-->

<div class="row">

<div class="col-md-12" ng-show="filter_data > 0">

<table class="table">

<thead>

<th>Name<a ng-click="client_sort_data('name');"><i class="glyphicon glyphicon-sort"></i></a></th>

<th>Slug<a ng-click="client_sort_data('slug');"><i class="glyphicon glyphicon-sort"></i></a></th>

</thead>

<tbody>

<tr dir-paginate="cdata in client_search = (file | filter:search | orderBy : base :reverse | itemsPerPage:10)">

<td>{{cdata.slug}}</td>

<td>{{cdata.name}}</td>

</tr>

</tbody>

</table>

</div>

<!-- Show message if no found any data records -->

<div class="col-md-12" ng-show="filter_data == 0">

<div class="col-md-12">

<h4>No records found..</h4>

</div>

</div>

<!-- Show pagination data records -->

<div class="col-md-12">

<div class="col-md-6" ng-show="filter_data > 0">

<dir-pagination-controls

max-size="5"

direction-links="true"

boundary-links="true" >

</dir-pagination-controls>

</div>

</div>

</div>

</div>

</div>


<!-- Include External Libs -->

<script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.6.5/angular.min.js"></script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/angular-ui-bootstrap/2.5.0/ui-bootstrap-tpls.min.js"></script>

<script src="dirPagination.js"></script>

<!-- https://raw.githubusercontent.com/michaelbromley/angularUtils/master/src/directives/pagination/dirPagination.js -->

<script src="hdtutoapp.js"></script>


</body>

</html>

Step 3: Create hdtutoapp.js file

var HDTutoApp = angular.module('HDTutoApp', ['ui.bootstrap', 'angularUtils.directives.dirPagination']);


HDTutoApp.filter('beginning_data', function() {

return function(input, begin) {

if (input) {

begin = +begin;

return input.slice(begin);

}

return [];

}

});


HDTutoApp.controller('controller', function($scope, $http, $timeout) {


$http.get('ajax_fetch.php').then(successCallback, errorCallback);

function successCallback(response){

//success code

$scope.file = response.data;

$scope.gridData = 1;

$scope.total_limit = 10;

$scope.filter_data = $scope.file.length;

console.log($scope.file.length);

$scope.total_users = $scope.file.length;

}

function errorCallback(error){

//error code

}


$scope.pos_page = function(parm_pageno) {

console.log(parm_pageno);

$scope.gridData = parm_pageno;

};

$scope.filter = function() {

$timeout(function() {

$scope.filter_data = $scope.client_search.length;

}, 20);

};

$scope.client_sort_data = function(base) {

$scope.base = base;

$scope.reverse = !$scope.reverse;

};

});

Step 4: Create ajax_fetch.php file

<?php


$conn = new mysqli('localhost', 'root', 'root', 'sole');


$sqlQuery = "select slug, name from tags order by id";


$sql_result = $conn->query($sqlQuery) or die($conn->error . __LINE__);

$fetch_data = [];


if ($sql_result->num_rows > 0) {

while ($data_row = $sql_result->fetch_assoc()) {

$fetch_data[] = $data_row;

}

}


$sql_resdata = json_encode($fetch_data);

echo $sql_resdata;


?>

Now you are ready to run example. You can also see demo from this link:


See Example Demo

I hope you found your best....