我有此当前查询,我拼命尝试获取结果分页(总列表 - 如果使用下拉菜单不使用过滤器,则使用下拉菜单约为6000行(,但是DON知道如何。我已经尝试了几个教程,但是我一直在显示完整列表,因此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> <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
}
用于分页,在大多数情况下,您需要2个查询,第一个来计算行总数每页行。
基本上两个查询是相同的,第一个是一个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
update :我迅速使用您的代码写了一些内容,它应该与次要更改(未测试(
一起使用$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());