Server-Side Paging with MySQL, PHP, and AngularJS

Question

This is my first post on this site, and while I have gone through the 2 min. tour please guide me in the right direction if this question seems misplaced or not in line with the site's rules.

I have created/modified a PHP script (below), that puts a MySQL table in a JSON format. I'm working with 700,000+ rows so I thought my best bet was to use pagination.

<?php 
require('connection.php');
header('Content-Type: application/json');

ini_set("max_execution_time", "-1");
ini_set("memory_limit", "-1");
ignore_user_abort(true);
set_time_limit(0);

// PortalActivity Table - JSON Creation

$limit = $_POST['limit']; //the pagesize
$start = $_POST['start']; //Offset
$dir = $_POST['dir']; // DESC or ASC
$sort = $_POST['sort'];// the column

switch ($sort) {
    case "CallerPhoneNum": $orderby = "CallerPhoneNum"; break;
    case "Carrier": $orderby = "Carrier"; break;
    case "TotalLookups": $orderby = "TotalLookups"; break;
    case "TotalBlocks": $orderby = "TotalBlocks"; break;
    case "TotalComplaints": $orderby = "TotalComplaints"; break;
    case "ActivityRanking": $orderby = "ActivityRanking"; break;
    case "CallerType": $orderby = "CallerType"; break;
}

$sql = "SELECT COUNT(*) AS count FROM portal.PortalActivity";
$result = $conn->query($sql);
$row = $result->fetch(PDO::FETCH_ASSOC);
$count = $row['count'];

$query = "SELECT * FROM portal.PortalActivity ORDER BY $orderby $dir LIMIT $start, $limit";

$result = $conn->query($query);

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $PortalActivity[] = array(
        'CallerPhoneNum' => $row['CallerPhoneNum'],
        'Carrier' => $row['Carrier'],
        'TotalLookups' => $row['TotalLookups'],
        'TotalBlocks' => $row['TotalBlocks'],
        'TotalComplaints' => $row['TotalComplaints'],
        'ActivityRanking' => $row['ActivityRanking'],
        'CallerType' => $row['CallerType']
        );
}

$myData = array('PortalActivity' => $PortalActivity, 'totalCount' => $count);

echo json_encode($myData);


?>

The code gives me the data in all the rows as well as a Total Count of the rows.

I have a bootstrap framework that uses AngularJS. I would like to put the data in a table using pagination. So, when ever a user clicks "next" or clicks to a new page, the server would only load the necessary records per page (10, 25, 50, or 100).

I know that I am needing to: 1.) Create the paging in my controller 2.) Get the data from my controller to my php file so it can correctly receive the right limits and offsets. 3.) Display this in my view

I am new to these languages and I have found myself in a time crunch to get this demo out. How can I get these files working together (PHP, JS, and HTML) to correctly display the paging I need?

This is my original controller that is just displaying the data in a filtered view. As shown below, I'm using $http.get to get the data. This controller works, but I'm needing to take it a step further with the paging:

 app.controller('counterCtrl', function($scope, $http, $filter, filterFilter) { 

     $scope.Unknown = 'UK';
     $scope.NonProfit = 'NP';
     $scope.Political = 'PL';
     $scope.Prison = 'PR';

    $scope.getCount = function(strCat) {
        return filterFilter( $scope.items, {CallerType:strCat}).length;
    }

    $http.get("http://xx.xxx.xx.xx/php/jsoncreatePA.php")
        .success(function (data) {
            $scope.items = data.PortalActivity;
            });
 })

This is part of the view I've created with the working controller:

<section id="main-content" class="animated fadeInRight">
  <div class="row">
        <div class="col-md-12">
                <div class="panel panel-default">
                  <div class="panel-heading">
                    <h3 class="panel-title">Unknown</h3>

                  </div>
                <div class="panel-body">
                <table id="example" class="table table-striped table-bordered" datatable="" cellspacing="0" width="100%">
                    <thead>
                        <tr>
                            <th>Caller Number</th>
                            <th>Total Lookups</th>
                            <th>Total Blocks</th>
                            <th>Total Complaints</th>
                            <th>Activity Percentage</th>
                        </tr>
                    </thead>

                    <tbody>
                        <tr data-ng-repeat = "x in items | filter: { CallerType : Unknown }">
                            <td>{{x.CallerPhoneNum}}</td>
                            <td>{{x.TotalLookups}}</td>
                            <td>{{x.TotalBlocks}}</td>
                            <td>{{x.TotalComplaints}}</td>
                            <td>{{x.ActivityRanking}}</td>
                        </tr>
                    </tbody>
                </table>
                </div>
            </div>
        </div>
    </div>
</section>

Answer 1

For anyone that has come across this question, I've found the answer to this problem. Do note, as Mr. DOS has mentioned in a previous comment, this is vulnerable to SQL injection and I WILL modify the PHP code later. But for right now this gives me what I need and maybe it will help others along the way.

There were a few changes in my PHP that I needed to do:

<?php 
require('connection.php');
header('Content-Type: application/json');

// NetworkActivity Table - JSON File Creation

$pagenum = $_GET['page'];
$pagesize = $_GET['size'];
$offset = ($pagenum - 1) * $pagesize;
$search = $_GET['search'];

if ($search != "") {
    $where = "WHERE Carrier LIKE '%" . $search . "%'";
} else {
    $where = "";
}

$sql = "SELECT COUNT(*) AS count FROM PortalActivity $where";
$result = $conn->query($sql);
$row = $result->fetch(PDO::FETCH_ASSOC);
$count = $row['count'];

$query = "SELECT * FROM portal.PortalActivity $where ORDER BY Carrier, CallerPhoneNum LIMIT $offset, $pagesize";

$result = $conn->query($query);

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $PortalActivity[] = array(
        'CallerPhoneNum' => $row['CallerPhoneNum'],
        'Carrier' => $row['Carrier'],
        'TotalLookups' => $row['TotalLookups'],
        'TotalBlocks' => $row['TotalBlocks'],
        'TotalComplaints' => $row['TotalComplaints'],
        'ActivityRanking' => $row['ActivityRanking'],
        'CallerType' => $row['CallerType']
        );
}

$myData = array('PortalActivity' => $PortalActivity, 'totalCount' => $count);

echo json_encode($myData);

?>

As for as the controller, we duplicated the paging and got the PHP $_GET from the $http.get in the URL with page, size, and search.

// Portal Activity Table Control
 app.controller('activityTableCtrl', function($scope, $http) {

    $scope.currentPage = 1;
    $scope.totalItems = 0;
    $scope.pageSize = 10;
    $scope.searchText = '';
    getData();

    function getData() {
     $http.get('http://xx.xxx.xx.xx/php/jsoncreatePA.php?page=' + $scope.currentPage + '&size=' + $scope.pageSize + '&search=' + $scope.searchText)
        .success(function(data) {
            $scope.activity = [];
            $scope.totalItems = data.totalCount;
            $scope.startItem = ($scope.currentPage - 1) * $scope.pageSize + 1;
            $scope.endItem = $scope.currentPage * $scope.pageSize;
            if ($scope.endItem > $scope.totalCount) {$scope.endItem = $scope.totalCount;}
            angular.forEach(data.PortalActivity, function(temp){
                $scope.activity.push(temp);
            });
        });
    }

    $scope.pageChanged = function() {
        getData();
    }
    $scope.pageSizeChanged = function() {
        $scope.currentPage = 1;
        getData();
    }
    $scope.searchTextChanged = function() {
        $scope.currentPage = 1;
        getData();
    }
 }) 

Finally, we changed up the view to accommodate the paging controls.

<section id="main-content" class="animated fadeInRight">
    <div class="row">
        <div class="col-md-12" ng-controller="activityTableCtrl">
            <div class="panel panel-default">
              <div class="panel-heading">
                <h3 class="panel-title">Unknown</h3>
              </div>
              <div class="panel-body">
              <!-- TOP OF TABLE: shows page size and search box -->
              <div class="dataTables_wrapper form-inline" role="grid">
                <div class="row">
                    <div class="col-sm-6">
                        <div class="dataTables_length" id="example_length">
                            <label>
                            <select name="example_length" aria-controls="example" class="form-control input-sm" ng-model="pageSize" ng-change="pageSizeChanged()">
                                <option value="10">10</option>
                                <option value="25">25</option>
                                <option value="50">50</option>
                                <option value="100">100</option>
                            </select> records per page</label>
                        </div>
                    </div>
                    <div class="col-sm-6">
                        <div id="example_filter" class="dataTables_filter">
                            <label>Search:<input type="search" class="form-control input-sm" aria-controls="example" ng-model="searchText" ng-change="searchTextChanged()"></label>
                        </div>
                    </div>
                </div>                        

                <!-- DATA TABLE: shows the results -->
                <!-- <table id="example" class="table table-striped table-bordered" datatable="" cellspacing="0" width="100%"> -->
                <table id="example" class="table table-striped table-bordered" cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th>Caller Number</th>
                        <th>Total Lookups</th>
                        <th>Total Blocks</th>
                        <th>Total Complaints</th>
                        <th>Activity Percentage</th>
                    </tr>
                </thead>

                <tbody>
                    <tr ng-repeat = "x in activity">
                        <td>{{x.CallerPhoneNum}}</td>
                        <td>{{x.TotalLookups}}</td>
                        <td>{{x.TotalBlocks}}</td>
                        <td>{{x.TotalComplaints}}</td>
                        <td>{{x.ActivityRanking}}</td>
                    </tr>
                </tbody>
                </table>

                <!-- BOTTOM OF TABLE: shows record number and pagination control -->
                <div class="row">
                    <div class="col-sm-6">
                        <div class="dataTables_info" ole="alert" aria-live="polite" aria-relevant="all">Showing {{startItem}} to {{endItem}} of {{totalItems}} entries</div>
                    </div>
                    <div class="col-sm-6">
                        <div class="dataTables_paginate paging_simple_numbers">
                        <pagination total-items="totalItems" ng-model="currentPage" ng-change="pageChanged()" items-per-page="pageSize" max-size="3" boundary-links="true" rotate="false" ></pagination>
                        </div>
                    </div>
                </div>                      
              </div>
              </div>
            </div>
        </div>
    </div>
 </section>

Answer 2

I hope this will be helpfull.

Angular js Searching Sorting and Pagination in Angular js ID Country Capital {{country.id}} {{country.country}} {{country.capital}}

<?php

$DB_HOST = '127.0.0.1';
$DB_USER = 'root';
$DB_PASS = '';
$DB_NAME = 'YOUR DATABASE NAME';
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

$pageSize = (isset($_GET['pageSize']) ? $_GET['pageSize'] : "");
$currentPage = (isset($_GET['currentPage']) ? $_GET['currentPage'] : 1);
$offset = ($currentPage -1) * $pageSize;
$sql = "select * from countries";
$count_set = $mysqli->query($sql) or die($mysqli->error . __LINE__);

 $query = "select distinct id,country,capital from countries LIMIT $offset,$pageSize";
$result = $mysqli->query($query) or die($mysqli->error . __LINE__);



$arr = array();
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $arr[] = $row;
    }
}
$myData = array('result' => $arr, 'totalItems' => $count_set->num_rows);

echo $json_response = json_encode($myData);
?>


app.js

var app = angular.module('angularjsTable', ['angularUtils.directives.dirPagination']);
app.controller('listdata', function ($scope, $http) {
//    var vm = this;
    $scope.countries = []; //declare an empty array
    $scope.currentPage = 1; // initialize page no to 1
    $scope.totalItems = 0;
    $scope.pageSize = 10; //this could be a dynamic value from a drop down

    $scope.getData = function (pageno) { // This would fetch the data on page change.
        //In practice this should be in a factory.
        $scope.countries = [];
        $http.get("your host path/your file name.php?pageSize=" + $scope.pageSize + "&currentPage=" + pageno).success(function (response) {
            //ajax request to fetch data into vm.data
            $scope.countries = response.result;  // data to be displayed on current page.
            $scope.totalItems = response.totalItems; // total data count.
        });
    };
    $scope.getData($scope.currentPage);
//
    $scope.sort = function (keyname) {
        $scope.sortBy = keyname;   //set the sortBy to the param passed
        $scope.reverse = !$scope.reverse; //if true make it false and vice versa
    }
});