php - 如何让 MySQL 查询与 PHP 分页一起使用？

Question

我试图根据我正在使用的查询来保持分页，但我的问题是，它只适用于分页的第一页，之后查询恢复为没有过滤器的标准页面(第一页显示我的过滤器，但第二页显示所有结果)。我想知道是否有一种有效的方法可以在我单击我的页面时继续我的过滤查询，我现在不知道如何实现这一点。这是我目前的代码:

<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css">

<style type="text/css">

#win-range, #gaa-range, #sv-range{
width: 160px;
font-size: 10px;
margin: 0 auto;     
}
#win-range a, #gaa-range a, #sv-range a{
margin-top: 0px !important;
padding: 0 !important;
}
</style>
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>

<script>
$(function(){

$("#win-range").slider({
range: true,
min: 1,
max: 1000,
values: [1, 1000],
slide: function(event, ui) {
    // in order to pass the user selected values to your app, we will use jQuery to prepopulate certain hidden form elements, then grab those values from the $_POST
    $("#minwins").val(ui.values[0]);
    $("#maxwins").val(ui.values[1]);
    $("#winamount").val(ui.values[0] + " - " + ui.values[1]);
}
});
$("#winamount").val($("#win-range").slider("values", 0) + " - " + $("#win-range").slider("values", 1));

});
$(function(){

$("#gaa-range").slider({
range: true,
min: 0,
max: 10,
values: [0, 10],
slide: function(event, ui) {
    // in order to pass the user selected values to your app, we will use jQuery to prepopulate certain hidden form elements, then grab those values from the $_POST
    $("#mingaa").val(ui.values[0]);
    $("#maxgaa").val(ui.values[1]);
    $("#gaaamount").val(ui.values[0] + " - " + ui.values[1]);
}
});
$("#gaaamount").val($("#gaa-range").slider("values", 0) + " - " + $("#gaa-range").slider("values", 1));

});
$(function(){

$("#sv-range").slider({
range: true,
min: 750,
max: 1000,
values: [750, 1000],
slide: function(event, ui) {
    // in order to pass the user selected values to your app, we will use jQuery to prepopulate certain hidden form elements, then grab those values from the $_POST
    $("#minsv").val(ui.values[0]);
    $("#maxsv").val(ui.values[1]);
    $("#svamount").val(ui.values[0] + " - " + ui.values[1]);
}
});
$("#svamount").val($("#sv-range").slider("values", 0) + " - " + $("#sv-range").slider("values", 1));

});
</script>

<?php 
include("includes/header.php");
include("includes/mysqli_connect.php");

$minwins = $_POST['minwins'];
$maxwins = $_POST['maxwins'];
$mingaa = $_POST['mingaa'];
$maxgaa = $_POST['maxgaa'];
$minsv = $_POST['minsv'];
$maxsv = $_POST['maxsv'];
$querySelection = $_POST['q'];
// FILTERING YOUR DB
$sortstats = $_GET['sortstats'];
$sortstatslow = $_GET['sortstatslow'];
// pagination
    $getcount = mysqli_query ($con,"SELECT COUNT(*) FROM Player");
    $postnum = mysqli_result($getcount,0);// this needs a fix for MySQLi upgrade; see custom function below
    $limit = 6; //how many blog posts per page you will see.
    if($postnum > $limit){
    $tagend = round($postnum % $limit,0);
    $splits = round(($postnum - $tagend)/$limit,0);

    if($tagend == 0){
    $num_pages = $splits;
    }else{
    $num_pages = $splits + 1;
    }

    if(isset($_GET['pg'])){
    $pg = $_GET['pg'];
    }else{
    $pg = 1;
    }
    $startpos = ($pg*$limit)-$limit;
    $limstring = "LIMIT $startpos,$limit";
    }else{
    $limstring = "LIMIT 0,$limit";
    }

    // MySQLi upgrade: we need this for mysql_result() equivalent
    function mysqli_result($res, $row, $field=0) { 
        $res->data_seek($row); 
        $datarow = $res->fetch_array(); 
        return $datarow[$field]; 
    }


?>
<div class="listingcontainer">
<div class="sidebar">
    <h3>Sort By:</h3>
    <a href="listings.php?sortstats=Wins">Most Wins</a>
    <a href="listings.php?sortstatslow=GAA">Best Goals Against</a>
    <a href="listings.php?sortstats=SavePerc">Best Save %</a>
    <hr/>
    <h3>Custom Filter</h3>
    <br/>
    <div class="custom-filter">
        <form name="filters" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" id="filters">
            <label for="winamount">Win Range:</label>
            <input type="text" id="winamount" />
            <div style="clear:both;"></div>
            <input type="hidden" id="minwins" name="minwins" value="0" />
            <input type="hidden" id="maxwins" name="maxwins" value="1000" />
           <div id="win-range"></div>
           <br/>
           <label for="gaaamount">GAA:</label>
            <input type="text" id="gaaamount" /><br />
            <div style="clear:both;"></div>
            <input type="hidden" id="mingaa" name="mingaa" value="0" />
            <input type="hidden" id="maxgaa" name="maxgaa" value="10" />
           <div id="gaa-range"></div>
            <br/>
           <label for="svamount">SV %:</label>
            <input type="text" id="svamount" /><br />
            <div style="clear:both;"></div>
            <input type="hidden" id="minsv" name="minsv" value="750" />
            <input type="hidden" id="maxsv" name="maxsv" value="1000" />
           <div id="sv-range"></div>
            <input type="submit" name="submit" id="submit"/>
        </form>
    </div>
</div>
<div class="main-listings">
<h1>Current NHL Goaltenders</h1>
<?php 

    $result = mysqli_query($con, "SELECT * FROM Player ORDER BY PlayerID ASC $limstring");

    if(isset($sortstats)) {
        $result = mysqli_query($con,"SELECT * FROM Player ORDER BY $sortstats DESC $limstring ") or die (mysql_error());
    }
    if(isset($sortstatslow)) {
        $result = mysqli_query($con,"SELECT * FROM Player ORDER BY $sortstatslow ASC $limstring ") or die (mysql_error());
    }
    if(isset($_POST['submit']))
    {
        $result = mysqli_query($con, "SELECT * FROM Player WHERE Wins BETWEEN '$minwins' AND '$maxwins' AND
                                                    GAA BETWEEN '$mingaa' AND '$maxgaa' AND  SavePerc BETWEEN '$minsv' AND '$maxsv'
                                                    ORDER BY PlayerID ASC $limstring") or die (mysql_error());
    }

    while($row = mysqli_fetch_array($result)){
        $name = $row['LastName'] . ", " . $row['FirstName'];
        $wins = $row['Wins'];
        $pid = $row['PlayerID'];
        $image = $row['Picture'];
        $gaa = $row['GAA'];
        $sv = $row['SavePerc'];
        echo "<div class=\"player-listing\">";
        echo "<div class=\"image-holder\">";
        echo "<span class=\"helper\"></span>";
        echo "<a href=\"viewplayer.php?playerId=$pid\"><img src=\"admin/thumbs/$image\" alt=\"$name\"></a>";
        echo "</div>";
        echo "<div style=\"clear:both;\"></div>";
        echo "<a href=\"viewplayer.php?playerId=$pid\">$name</a>";
        echo "<table align=\"center\">";
        echo "<tr>";
        echo "<td style=\"border-bottom: 1px solid #212121;\">Wins</td>";
        echo "<td style=\"border-bottom: 1px solid #212121;\">GAA</td>";
        echo "<td style=\"border-bottom: 1px solid #212121;\">SV%</td>";
        echo "</tr>";
        echo "<tr>";
        echo "<td>$wins</td>";
        echo "<td>$gaa</td>";
        echo "<td>.$sv</td>";
        echo "</tr>";
        echo "</table>";
        echo "</div>";
    }

    // paging links:
    echo "<div class=\"paging\">";
    if($postnum > $limit){
        echo "<span class=\"page-numbers\"><strong>Pages:</strong> &nbsp;&nbsp;&nbsp;</span>";
        $n = $pg + 1;
        $p = $pg - 1;
        $thisroot = $_SERVER['PHP_SELF'];
        if($pg > 1){
            echo "<a href=\"$thisroot?pg=$p\"><< prev</a>&nbsp;&nbsp;";
        }
        for($i=1; $i<=$num_pages; $i++){
            if($i!= $pg){
                echo "<a href=\"$thisroot?pg=$i\">$i</a>&nbsp;&nbsp;";
            }else{
                echo "$i&nbsp;&nbsp;";
            }
        }
        if($pg < $num_pages){
            // INSERT QUERY STRING VARIBLE TO CARRY OVER DB QUERY
            echo "<a href=\"$thisroot?pg=$n\">next >></a>";
        }
        echo "&nbsp;&nbsp;";
    }
    // end paging
    echo "</div>";
?>
</div>
<div style="clear:both;"></div>

Answer 1

在 session 中保存您的搜索过滤器，并始终检查是否为任何搜索设置了 session 值，并将该数据传递给您的查询。直到并且除非没有人重置该搜索条件并且当您的用户将该搜索条件重置为 null 然后放该 session 搜索变量的 session 中的值空白。