php - 结果分页(mysql+php)取决于下拉菜单过滤器

Question

我有这个当前查询，并且我拼命地尝试对结果进行分页(总列表 - 如果没有使用下拉菜单应用过滤器，则大约有 6000 行)，但不知道如何进行。我已经尝试了几个教程，但我不断显示完整列表，因此 ceil() 函数永远不会发挥作用。我认为这是因为我的查询不是为了允许由于下拉菜单而对查询中的总行数进行简单计数而构建的。

任何人都可以帮助我围绕此查询构建分页吗？

$sql = "SELECT official_status, official_expiration_date, membership_status,membership_expiration_date, player_number, first_name, last_name, classification, gender, birth_year, rating, rating_effective_date, country, code, country.description, bracket.id, bracket.min, bracket.max, cutoff FROM Players, country, bracket, ratingscutoff WHERE membership_expiration_date >= cutoff AND Players.country = country.code ";

if (!empty($_GET['class'])) {
$filter_class = $_GET['class'];
$sql .= "AND classification = '{$filter_class}' ";
}
if (!empty($_GET['gender'])) {
$filter_gender = $_GET['gender'];
$sql .= "AND gender = '{$filter_gender}' ";
}
if (!empty($_GET['country'])) {
$filter_country = $_GET['country'];
$sql .= "AND country = '{$filter_country}' ";
}
if (empty($_GET['bracket'])) {
$filter_bracket = $_GET['bracket'];      
$sql .= "AND bracket.id = '0' AND Players.birth_year BETWEEN bracket.min AND bracket.max ";      
}        
else         
{
if (!empty($_GET['bracket'])) {
$filter_bracket = $_GET['bracket'];
$sql .= "AND bracket.id = '{$filter_bracket}' AND Players.birth_year BETWEEN bracket.min AND bracket.max ";
}
}
$sql .= "ORDER BY rating DESC, player_number ASC";

$result = mysqli_query($link, $sql) or die("Unable to select: ".mysqli_error());
$num_rows = mysqli_num_rows($result);
$counter = 1;
while ($row = mysqli_fetch_array($result)) {
printf("<tr>
<td class=\"stats\">$counter</td>
<td nowrap class=\"stats\"><b><a href=\"/player/%s\" target=\"_blank\">%s %s</a> &nbsp;&nbsp; <img src=\"/wp-content/uploads/2014/12/%s.png\" title=\"certified official until %s\"/></b>
</td>
<td class=\"stats\"><span title=\"current member through %s\">%s</span></td>
<td class=\"stats\"><span title=\"rating updated %s\">%s</span></td>
<td class=\"stats\">%s</td>
<td class=\"stats\">%s</td>
<td class=\"stats\">%s</td>
\n",
$row["player_number"], $row["first_name"], $row["last_name"], $row["official_status"], $row["official_expiration_date"], $row["membership_expiration_date"], $row["player_number"], $row["rating_effective_date"], $row["rating"],  $row["classification"], $row["gender"], $row["description"]);
$counter++; //increment counter by 1 on every pass 
}

Answer 1

对于分页，在大多数情况下，您需要 2 个查询，第一个查询计算总行数，第二个查询使用 LIMIT 实际返回行集，其中包含最后一个偏移量和每页的行数。

基本上，这两个查询是相同的，第一个查询只是 COUNT()。运行它，获取总数(在我的示例中将其设置在 $num_rows 变量中)，然后设置一个变量以获取每页的行数(假设 $rows_per_page = 10;)，然后在第二个查询之前，做类似的事情:

if (empty($_GET['page'])) {
        $page   = 1;
        $offset = $rows_per_page;
    } else {
        $num_pages = floor($num_rows / $rows_per_page);

        if ($_GET['page'] > $num_pages) {
            $page   = $num_pages;
        } else {
            $page   = $_GET['page'];
        }

        $offset = $rows_per_page * $page;
    }

您现在对第二个查询有限制:

LIMIT '.$offset.', '.$rows_per_page

更新:我很快用你的代码写了一些东西，它应该可以进行微小的更改(未经测试)

$rows_per_page = 10;

$sql_count = "
    SELECT 
        COUNT(*)
    FROM 
        Players, country, bracket, ratingscutoff 
    WHERE membership_expiration_date >= cutoff 
    AND Players.country = country.code 
";
$result_count = mysqli_query($link, $sql_count) or die("Unable to select: ".mysqli_error());
$num_rows = mysqli_num_rows($result_count);

if (empty($_GET['page'])) {
    $page   = 1;
    $offset = $rows_per_page;
} else {
    $num_pages = floor($num_rows / $rows_per_page);

    if ($_GET['page'] > $num_pages) {
        $page   = $num_pages;
    } else {
        $page   = $_GET['page'];
    }

    $offset = $rows_per_page * $page;
}

// Your other filters are being omitted for more clarity
$sql_select = "
    SELECT 
        official_status, official_expiration_date, membership_status,membership_expiration_date, player_number, first_name, last_name, classification, gender, birth_year, rating, rating_effective_date, country, code, country.description, bracket.id, bracket.min, bracket.max, cutoff
    FROM 
        Players, country, bracket, ratingscutoff 
    WHERE membership_expiration_date >= cutoff 
    AND Players.country = country.code 
    ORDER BY rating DESC, player_number ASC
    LIMIT ".$offset.", ".$rows_per_page."
";

$result = mysqli_query($link, $sql_select) or die("Unable to select: ".mysqli_error());