如何用PHP和SQL中的过滤器解决分页问题? [英] How to fix pagination problem with filter in PHP and SQL?
问题描述
PHP爱好者在这里.
PHP enthusiast here..
我有一个脚本，可以在其中执行查询，并将整个数据库行放入表单元格并进行分页.
I have a script where it does a query and brings the entire database rows into table cells and it's paginated.
问题是，当用户在搜索框中键入某些内容以过滤分页时，它仅在显示过滤结果的第1页上起作用，但随后在第2页上停止工作，第2页返回到所有分页结果并忽略过滤器.
The problem is when a user types something in the search box to filter the pagination, it only works in page 1 showing the filtered results but then it stops working on page 2, on page 2 is goes back to all the paginated results and ignores the filter.
如何将过滤器值带入url，以便它在整个分页中均保持不变?
How do I bring the filter value into the url so it stays throughout the pagination?
对不起，很明显我只是想不通
Sorry if it's obvious I just can't figure it out
<?php require('includes/config.php');
//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: index.php'); exit(); }
//include header template
require('layout/header.php');
$stmt = $db->query("SELECT name, last_name FROM `designers` WHERE `username` = '" . $_SESSION['username'] . "'");
$result1 = $stmt->fetch(PDO::FETCH_ASSOC);
$name = $result1['name'];
$last_name = $result1['last_name'];
if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
}
$no_of_records_per_page = 15;
$offset = ($pageno-1) * $no_of_records_per_page;
$conn=mysqli_connect("xxxx", "xxxx", "xxxx", "xxxx");
$total_pages_sql = "SELECT COUNT(*) FROM protonumbers WHERE designer LIKE '".$name.'_'.$last_name."'";
$result = mysqli_query($conn,$total_pages_sql);
$total_rows = mysqli_fetch_array($result)[0];
$total_pages = ceil($total_rows / $no_of_records_per_page);
?>
<style>
table {
border-collapse: collapse;
width: 100%;
}
th, td {
text-align: center;
padding: 8px;
}
tr:nth-child(even){background-color: #f2f2f2}
th {
background-color: #428bca;
color: white;
}
</style>
<div class="container">
<div class="row">
<div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
<form action="search.php" autocomplete="off" method="post">
<h3>My Proto Numbers</h3><hr><br>
<?php
//check for any errors
if(isset($error)){
foreach($error as $error){
echo '<p class="bg-danger">'.$error.'</p>';
}
}
?>
</div>
</div>
</div>
<?php
if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT * FROM `protonumbers` WHERE UPPER(CONCAT(`brand`, `protoID`, `season`, `program`, `protonumber`)) LIKE UPPER('%".$valueToSearch."%') AND designer LIKE '".$name.'_'.$last_name."' LIMIT $offset, $no_of_records_per_page";
$search_result = filterTable($query);
}
else {
$query = "SELECT * FROM `protonumbers` WHERE designer LIKE '".$name.'_'.$last_name."' LIMIT $offset, $no_of_records_per_page";
$search_result = filterTable($query);
}
// function to connect and execute the query
function filterTable($query)
{
$connect = mysqli_connect("xxxx", "xxxx", "xxxx", "xxxx");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}
?>
<div class="container">
<div class="row">
<div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
<div class="form-group">
<input type="text" name="valueToSearch" class="form-control input-lg" placeholder="Search by Proto number, Season, Brand, Program" tabindex="1">
<div class="row"><br />
<div class="col-xs-6 col-md-6">
<input type="submit" name="search" value="Search" class="btn btn-primary btn-block btn-lg" tabindex="2"> .
</form></div></div></div></div>
<div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
<a href="index.php">Back</a><br><br> <ul class="pagination">
<li><a href="?pageno=1">First</a></li>
<li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
<a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>">Prev</a>
</li>
<li class="<?php if($pageno >= $total_pages){ echo 'disabled'; } ?>">
<a href="<?php if($pageno >= $total_pages){ echo '#'; }
else { echo "?pageno=".($pageno + 1); } ?>">Next</a>
</li>
<li><a href="?pageno=<?php echo $total_pages; ?>">Last</a></li>
</ul>
<table>
<tr>
<th>ProtoID</th>
<th>Brand</th>
<th>Season</th>
<th>Program</th>
<th>Proto Number</th>
</tr>
<!-- populate table from mysql database -->
<?php while($row =
mysqli_fetch_array($search_result)):?>
<tr>
<td><?php echo $row['protoID'];?></td>
<td><?php echo $row['brand'];?></td>
<td><?php echo $row['season'];?></td>
<td><?php echo $row['program'];?></td>
<td><?php echo $row['protonumber'];?></td>
</tr>
<?php endwhile;?>
</table>
</div></div>
<?php
//include header template
require('layout/footer.php');
?>
推荐答案
好吧，因此，正如注释中所述，归结为HTTP不是有状态的，您将需要在请求中保留这些过滤器值.因此，可以将内容存储在会话中，或者只是将URI查询字符串保持在状态中.
Ok, so, as stated in the comments this boils down to HTTP not being stateful, you'll need to persist those filters values across requests. So, storing things in say session, or simply keeping the URIs query string in state.
因此构建该查询字符串...
So building out that query string...
<?php
$assignedFilters = [];
if (!empty($name)) $assignedFilters['name'] = $name;
if (!empty($last_name)) $assignedFilters['last_name'] = $last_name;
$filterParams = http_build_query($assignedFilters);
?>
然后在您的分页链接的href上获取它...这是一个示例
And then getting it on the href for you pagination links... Here is one example
<a href="<?php if($pageno <= 1) { echo '#';
} else {
echo "?pageno=".($pageno - 1) . '&' . $filterParams; } ?>">Prev</a>
换句话说，将. '&' . $filterParams附加到现有的分页链接上.
In other words append . '&' . $filterParams to your existing pagination links.
这篇关于如何用PHP和SQL中的过滤器解决分页问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
