在多个页面之间分离SQL查询

Question

几个小时以来，我一直在努力解决如何做到这一点，我已经设法使用OFFSET和FETCH获得了查询，它们将设置要显示的结果数量和从哪个数字开始，但似乎无法解决如何在页脚中实际使用分页，以便在新页面上显示。

html、css、php和mysql都是新手，所以这是一个边学边学的教程，但很难找到有意义的教程。

<?php

$sql = "SELECT STEAM_ID, Hunters, Smoker, Boomers, Spitters, Jockeys, Charger,
(Hunters + Smoker + Boomers + Spitters + Jockeys + Charger) as Total_Kills
FROM Special_Infected_Kills ORDER BY Total_Kills DESC";
$result = $conn->query($sql);
if ($result = $conn->query($sql)) {
echo "<table><tr>
<th>Player</th>
<th>Total Kills</th>
<th>Hunter</th>
<th>Jockey</th>
<th>Charger</th>
<th>Smoker</th>
<th>Boomer</th>
<th>Spitter</th>
</tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>".$row["STEAM_ID"]."</td>
<td>".$row["Total_Kills"]."</td>
<td>".$row["Hunters"]."</td>
<td>".$row["Boomers"]."</td>
<td>".$row["Spitters"]."</td>
<td>".$row["Jockeys"]."</td>
<td>".$row["Charger"]."</td>
<td>".$row["Smoker"]."</td>
</tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>

我的页脚

<footer>
<div class="center">
<div class="pagination">
<a href="#">&laquo;</a>
<a href="#" class="active">1</a>
<a href="#">2</a>
<a href="#">3</a>
<a href="#">4</a>
<a href="#">5</a>
<a href="#">6</a>
<a href="#">&raquo;</a>
</div>
</div>
<div class="createdby">
<p> Website built & designed by Blade </p>
</div>
</footer>

所以很可能只想在第一页上显示前20个结果，然后通过点击页脚中的分页在另一页上每隔20个结果

Answer 1

您在问题中提到了FETCH，但不清楚您是否引用了javascriptfetchapi或其他未公开的内容，但以下可能有助于解决分页结果的问题。在没有相关数据库表和数据的情况下，使用另一个表和记录集对以下内容进行了测试，以产生分页结果-似乎工作正常。我相信，如果需要，可以很容易地将其调整为使用fetchapi。

<?php
/* count all records to begin pagination calculations */
$sql='select count(*) as `rows` from `special_infected_kills`';
$res=$db->query( $sql );

$rs=$res->fetch_object();
$rows=$rs->rows;                    // total number of records in table

$pagesize=20;                       // results per page
$pages=ceil( $rows / $pagesize );   // calculated total number of pages


/*
deduce or generate the current page. As we want to display ?page=1 rather than ?page=0
in the querystring the hyperlinks for paging start at 1 but the recordset pages start 
at zero. Hence subtracting 1 immediately from specified page.

Use the Elvis operator to set alternative value if the filter fails.
*/
$page=filter_input( INPUT_GET, 'page', FILTER_SANITIZE_NUMBER_INT ) ?: 1;
$page--;

// ensure the $page is within correct bounds
if( $page < 0 )$page=1;
if( $page > $pages )$page=$pages;


// construct sql for paged results, supply limit clauses as placeholders.
$sql = 'SELECT 
STEAM_ID, Hunters, Smoker, Boomers, Spitters, Jockeys, Charger,
( Hunters + Smoker + Boomers + Spitters + Jockeys + Charger ) as `Total_Kills`
FROM `Special_Infected_Kills` 
ORDER BY `Total_Kills` DESC
LIMIT ?, ?';


// create the prepared statement, bind and run. Assign results to named variables.
$stmt=$db->prepare( $sql );
$stmt->bind_param('ss', $page, $pagesize );
$res=$stmt->execute();
$stmt->bind_result( $sid, $hunters, $smoker, $boomers, $splitters, $jockeys, $charger, $kills );



// display the data for the current page
$output='
<table>
<tr>
<th>Player</th>
<th>Total Kills</th>
<th>Hunter</th>
<th>Jockey</th>
<th>Charger</th>
<th>Smoker</th>
<th>Boomer</th>
<th>Spitter</th>
</tr>';

while( $rs=$stmt->fetch() ){
$output .= sprintf('
<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
</tr>', $sid, $kills, $hunters, $jockeys, $charger, $smoker, $boomers, $splitters );
}
$output .= '
</table>';


/************************************************
Links for First page, previous, next & last 
are calculated differently.
*/
$first= $page > 0 ? '<a class="paging" href="?page=1">First</a>' : '<span>First</span>';
$prev = $page > 0 ? sprintf('<a class="paging" href="?page=%s">Previous</a>',( max( 1, $page ) ) ) : '<span>Previous</span>';
$next = $page < $pages ? sprintf('<a class="paging" href="?page=%s">Next</a>',( min( $pages + 1, $page + 2 ) ) ) : '<span>Next</span>';
$last = $page < $pages ? sprintf('<a class="paging" href="?page=%s">Last</a>', $pages + 1 ) : '<span>Last</span>';


$footer=sprintf('
<footer>
<div class="center">
<div class="pagination">

<!-- text links for prev/next etc -->
<div>%s | %s | %s | %s</div>

<a class="paging" href="?page=%s">&laquo;</a>', $first, $prev, $next, $last, ( max( 1, $page ) ) );


for( $i=1; $i <= $pages + 1; $i++ ){
$active=$page == $i ? ' active' : '';
$footer .= sprintf(' <a class="paging%2$s" href="?page=%1$d">%1$d</a> ', $i, $active );
}



$footer .= sprintf('
<a class="paging" href="?page=%s">&raquo;</a>
</div>
</div>

<div class="createdby">
<p>Website built & designed by Blade</p>
</div>
</footer>', min( $pages + 1, $page + 2 ));



// render the html
echo $output, $footer;

?>