小贝子编程

应用 PHP 分页不起作用 记录筛选器



我编写了一个PHP脚本，该脚本可以选择过滤记录并按页面显示过滤的记录。

我的问题： 当我应用过滤器时，会显示记录，但是当我单击任何页码时，它会按页面显示所有记录，而不是按页面显示过滤的记录。

我该如何纠正此问题？
这是我的完整脚本，

<body>
<form style="background-color:darkorange; font-size: 13px;" id="form1" name="form1" method="post" action="displaydesign.php">
<label>Design Category</label>
<select name="dlocation">
<option value="">All</option>
<?php
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY dlocation ORDER BY dlocation";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
while ($row = mysql_fetch_assoc($sql_result)) {
echo "<option value='".$row["dlocation"]."'".($row["dlocation"]==$_REQUEST["dlocation"] ? " selected" : "").">".$row["dlocation"]."</option>";
}
?>
</select>
<input type="text" name="string" id="string" value="<?php echo stripcslashes($_REQUEST["string"]); ?>" placeholder="Search by Name or City" />
<input type="submit" name="button" id="button" value="Filter" />
<a style="background-color:white;" href="displaydesign.php"> Reset</a>
</form>
<hr>
<?php
if ($_REQUEST["string"]<>'') {
$search_string = " AND (dname LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%' OR dcity LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%')"; 
}
if ($_REQUEST["dlocation"]<>'') {
$search_dlocation = " AND dlocation='".mysql_real_escape_string($_REQUEST["dlocation"])."'";    
}
$per_page=2;    // no.of records per page
if (isset($_GET["page"])) {
$page = $_GET["page"];
}
else {
$page=1;
}
// Page will start from 0 and Multiple by Per Page
$start_from = ($page-1) * $per_page;
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE did>0".$search_string.$search_dlocation." order by did desc LIMIT $start_from, $per_page";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
if (mysql_num_rows($sql_result)>0) {?>
<div class="row">
<?php while ($row = mysql_fetch_assoc($sql_result)) { ?>
<div class="col-sm-6">
<div class="card" >
<h3 class="card-header card-success text-center"><?php echo $row['dlocation'] ?></h3>
<img class="card-img-top img-fluid" src="<?php echo $row['dimage'] ?>" alt="Card image cap">
<div class="card-block ">
<h4><span class="badge badge-default">Designer Information</span></h4>
<h5 class="card-title"><?php echo $row['dname'] ?></h5>
<h6 class="card-subtitle mb-2 text-muted "><?php echo ($row['dcity'])?></h6>
<p class="text-right">
<a class="btn btn-danger btn-sm " data-toggle="collapse" href="#collapseExample<?php echo ($row['did'])?>" aria-expanded="false" aria-controls="collapseExample<?php echo ($row['did'])?>">
Know More..
</a>
</p>
<div class="collapse" id="collapseExample<?php echo ($row['did'])?>">
<div class="card card-block">
<h5><span class="badge badge-warning">Contact Info</span></h5>
<h6 class="card-subtitle mb-2 text-muted"><a href="mailto:<?php echo ($row['demail'])?>" target="_top"><?php echo ($row['demail'])?></a></h6>
<h6 class="card-subtitle mb-2 text-muted"><?php echo ($row['dmobile'])?></h6>
<h6 class="card-subtitle mb-2 text-muted"><?php echo ($row['daddress'])?></h6>
<h6 class="card-subtitle mb-2 text-muted"><?php echo ($row['dcity'])?></h6>
<h6 class="card-subtitle mb-2 text-muted"><a href="<?php echo ($row['dwebsite'])?>"><?php echo ($row['dwebsite'])?></a></h6>
<!--ACCORDION START-->
<div id="accordion<?php echo ($row['did'])?>" role="tablist" aria-multiselectable="true">
<div class="card">
<div class="card-header" role="tab" id="headingOne<?php echo ($row['did'])?>">
<h5 class="mb-0 btn-sm">
<a data-toggle="collapse" data-parent="#accordion<?php echo ($row['did'])?>" href="#collapseOne<?php echo ($row['did'])?>" aria-expanded="true" aria-controls="collapseOne<?php echo ($row['did'])?>">
Image Description
</a>
</h5>
</div>
<div id="collapseOne<?php echo ($row['did'])?>" class="collapse show" role="tabpanel" aria-labelledby="headingOne<?php echo ($row['did'])?>">
<div class="card-block">
<?php echo $row['dimagedescription'] ?>
</div>
</div>
</div>
<div class="card">
<div class="card-header" role="tab" id="headingTwo<?php echo ($row['did'])?>">
<h5 class="mb-0 btn-sm">
<a class="collapsed" data-toggle="collapse" data-parent="#accordion<?php echo ($row['did'])?>" href="#collapseTwo<?php echo ($row['did'])?>" aria-expanded="false" aria-controls="collapseTwo<?php echo ($row['did'])?>">
Software Used
</a>
</h5>
</div>
<div id="collapseTwo<?php echo ($row['did'])?>" class="collapse" role="tabpanel" aria-labelledby="headingTwo<?php echo ($row['did'])?>">
<div class="card-block">
<p class="card-text"><?php echo $row['dsoftwareused'] ?></p>
</div>
</div>
</div>
<div class="card">
<div class="card-header" role="tab" id="headingThree<?php echo ($row['did'])?>">
<h5 class="mb-0 btn-sm">
<a class="collapsed" data-toggle="collapse" data-parent="#accordion<?php echo ($row['did'])?>" href="#collapseThree<?php echo ($row['did'])?>" aria-expanded="false" aria-controls="collapseThree<?php echo ($row['did'])?>">
My Brand Recommendation
</a>
</h5>
</div>
<div id="collapseThree<?php echo ($row['did'])?>" class="collapse" role="tabpanel" aria-labelledby="headingThree<?php echo ($row['did'])?>">
<div class="card-block">
<div class="card">
<ul class="list-group list-group-flush">
<li class="list-group-item"><?php echo $row['dbrandname'] ?></li>
<li class="list-group-item"><?php echo $row['dbrandsegment'] ?></li>
<li class="list-group-item"><?php echo $row['dbrandwebsite'] ?></li>
<li class="list-group-item"><?php echo $row['dbrandemail'] ?></li>
</ul>
</div>
</div>
</div>
</div>
</div>
<!--ACCORDION END-->
</div>
</div>              
</div>
<div class="card-footer">
<small class="text-muted">Design ID:- <?php echo stripcslashes($row['did']) ?> Submitted on :-<?php echo stripcslashes($row['dsubmissiondate']) ?></small>
<br>
</div>
</div>
</div>
<?php } ?>
</div>
<?php } else { ?>
<h3>No results found for the desired Search.</h3>
<?php } ?>
<!-- jQuery first, then Tether, then Bootstrap JS. -->
<script src="https://code.jquery.com/jquery-3.1.1.slim.min.js" integrity="sha384-A7FZj7v+d/sdmMqp/nOQwliLvUsJfDHW+k9Omg/a/EheAdgtzNs3hpfag6Ed950n" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tether/1.4.0/js/tether.min.js" integrity="sha384-DztdAPBWPRXSA/3eYEEUWrWCy7G5KFbe8fFjk5JAIxUYHKkDx6Qin1DkWx51bBrb" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.6/js/bootstrap.min.js" integrity="sha384-vBWWzlZJ8ea9aCX4pEW3rVHjgjt7zpkNpZk+02D9phzyeVkE+jo0ieGizqPLForn" crossorigin="anonymous"></script>
<div>
<?php       //for page numbers display at bottom of page
//Now select all from table for pagination 
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE did>0".$search_string.$search_dlocation." order by did desc";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
// Count the total records
$total_records = mysql_num_rows($sql_result);
//Using ceil function to divide the total records on per page
$total_pages = ceil($total_records / $per_page);
//Going to first page
echo "<center><a href='displaydesign.php?page=1'>".'First Page'."</a> ";
for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='displaydesign.php?page=".$i."'>".$i."</a> ";
};
// Going to last page
echo "<a href='displaydesign.php?page=$total_pages'>".'Last Page'."</a></center> ";
?>
</div>
</body>

我一直在查看您的代码，并且我还设置了一个 SQL-Fiddle(见下文)来玩。我最初的想法可能是您使用的 MySQL 查询没有正确过滤特定值，或者没有正确分页但正确过滤。无论如何，小提琴中也有评论解释了我所做的一些想法和假设。

就其价值而言，分页和复杂的SQL查询并非易事。它们也可能很昂贵，虽然我不知道您当前的代码平台/设置/环境，但我建议尽可能将逻辑和分页等东西推送到前端或 JavaScript。PHP 也可以工作，但我主要是想指出，根据我的经验，SQL 查询的复杂性通常比代码增加得更快，因为它是一种不同类型的框架集，即关系框架集和过程框架集。

您的代码还包含视图和业务逻辑以及SQL，因此与您开放，我可能找不到问题，或者可能有多个问题。但我确实注意到一些东西在看你的代码。

(1) 联系人列表查询的分页结果不是按页码排除的 - 这意味着在某些情况下，结果中将包含先前结果中的一行。

(2)如果您的WHERE条件值的每个空值都存在任何空值，则存在奇怪的行为 - 特别是$dname，$dcity，$dlocation。我没有设法从SQL小提琴中的SQL中重现您的确切问题，但是我想知道您是否会在清理列表查询时看到相同的问题。

修复建议：

  • 修改 SQL 查询以使用排名和内部查询来独占分页结果并消除空匹配问题。在下面和小提琴中，我将 WHERE 条件从 AND(b 或 c)和 d 调整为 AND((b 或 c)或 d )——虽然这看起来很奇怪，但它说"确保我们在非空位置匹配(假设的主要选择器)，或者我们有一个名称或城市可以过滤"。对于 LIKE 条件检查，至少有一个变量必须与 true 匹配，否则它不会为其返回结果行，而另一种方式需要一行的所有三个匹配项(如果这些值始终为非空，这很好)。

    SET @name = "";
SET @city = "";
SET @location = "WA";
SET @per_page = 1;
-- Setting this arbitrarily - looking at different pages
-- mostly verifying yeah, the fetch/ pagination part works.
SET @page = 3;
SET @rownum = 0;
-- Basic Fetch Query used for getting list-for-page
SELECT
results.id, results.name, 
results.location, results.city,
results.rank
FROM (
SELECT
c.id AS id
,c.name AS name
,c.location AS location
,c.city AS city
,@rownum := @rownum + 1 AS rank
FROM contacts AS c
-- ,(SELECT @rownum := 0) r
WHERE c.id > 0
AND ( (c.name LIKE @name OR c.city LIKE @city)
OR c.location LIKE @location )
ORDER BY c.id ASC
) AS results
WHERE rank BETWEEN ( (@page-1) * @per_page + 1) AND ( (@page) * @per_page )
<小时 />

澄清

在这个更大的查询中发生了 2 件新事情。首先，有一种称为"内部查询">的东西用于预先选择一组行。其次，我分配一个排名值，用于在最终结果查询中，以将表行组织成"分页"结果。这是尝试使用的解决方法

WHERE ...
LIMIT ((@page-1)*@per_page), @per_page

甚至

WHERE ...
LIMIT ((@page-1)*@per_page) OFFSET @per_page

但是(请参阅我的CREDITS部分)，显然不能在SQL中使用@vars。你也可以通过使用PHP字符串模板或其他东西在你的代码中解决这个问题，就像你已经在做的将变量注入字符串一样。

SQL Fiddle

这是SQL小提琴，看看我做了什么假设以及我在做什么：

http://sqlfiddle.com/#!9/d516e/26

<小时 />

学分

显然，不能在 LIMIT 子句中使用像 @name 这样的用户变量，这是我以前不知道的。我找到了另一个 Stack 帖子来帮助我解决这个问题： 在 MySQL 的 LIMIT 子句中使用变量

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium