这是我在这个网站上的第一篇文章，虽然我已经过了 2 分钟。如果这个问题似乎放错地方或不符合网站规则，请指导我正确的方向。

我已经创建/修改了一个 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);


?>

代码为我提供了所有行中的数据以及行的总计数。

我有一个使用 AngularJS 的 Bootstrap 框架。我想使用分页将数据放在表格中。因此，当用户点击“下一步”或点击新页面时，服务器只会在每页加载必要的记录(10、25、50 或 100)。

我知道我需要:1.) 在我的 Controller 中创建分页 2.) 将数据从我的 Controller 获取到我的 php 文件，以便它可以正确接收正确的限制和偏移量。 3.) 在我的 View 中显示这个

我是这些语言的新手，我发现自己在时间紧迫中无法完成这个演示。如何让这些文件(PHP、JS 和 HTML)协同工作以正确显示我需要的分页？

这是我原来的 Controller ，它只是在过滤 View 中显示数据。如下所示，我使用 $http.get 来获取数据。这个 Controller 可以工作，但我需要在分页方面更进一步:

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

这是我使用工作 Controller 创建的 View 的一部分:

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

最佳答案

对于遇到这个问题的任何人，我已经找到了这个问题的答案。请注意，正如 DOS 先生在之前的评论中提到的那样，这容易受到 SQL 注入(inject)的攻击，稍后我将修改 PHP 代码。但就目前而言，这给了我我所需要的，也许它会帮助其他人。

我需要对我的 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);

?>

至于 Controller ，我们复制了分页并从 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();
    }
 }) 

最后，我们更改了 View 以适应分页控件。

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

关于javascript - 使用 MySQL、PHP 和 AngularJS 的服务器端分页，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31301637/