使用MySQL，PHP和AngularJS进行服务器端分页

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.

这是我在这个网站上的第一篇文章,虽然我经历了2分钟。如果这个问题看起来错位或不符合网站的规则,请引导我朝正确的方向前进。

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脚本(如下所示),它将MySQL表格放在JSON格式中。我正在使用700,000多行,所以我认为我最好的选择是使用分页。

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

我有一个使用AngularJS的bootstrap框架。我想使用分页将数据放在表中。因此,当用户点击“下一步”或点击新页面时,服务器只会每页加载必要的记录(10,25,50或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

我知道我需要:1。)在我的控制器中创建分页2.)从我的控制器获取数据到我的php文件,以便它可以正确地接收正确的限制和偏移。 3.)在我的视图中显示

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?

我是这些语言的新手,我发现自己很快就得到了这个演示。如何让这些文件一起工作(PHP,JS和HTML)以正确显示我需要的分页?

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:

这是我的原始控制器,它只是在过滤后的视图中显示数据。如下所示,我使用$ http.get来获取数据。这个控制器工作,但我需要更进一步的分页:

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

     $scope.Unknown = 'UK';
     $scope.NonProfit = 'NP';
     $scope.Political = 'PL';
     $scope.* = '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>

3 个解决方案

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

对于遇到过这个问题的人,我找到了这个问题的答案。请注意,正如DOS先前在评论中提到的那样,这很容易受到SQL注入的影响,我将在稍后修改PHP代码。但是现在这给了我我需要的东西,也许它会帮助其他人。

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

我需要做的PHP中有一些变化:

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

至于控制器,我们复制了分页,并通过页面,大小和搜索从URL中的$ http.get获得PHP $ _GET。

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

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

Angular js在Angular js ID国家资本{{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
    }
});

#3

You can easily paging using client side cause if use server side every page number button click page refresh and and get data form database. It is now looking quality full. So solve this quality work use jQuery data table.

如果使用服务器端每个页码按钮单击页面刷新并获取数据表单数据库,则可以使用客户端原因轻松进行分页。它现在看起来质量十足。所以解决这个质量工作使用jQuery数据表。