PHP中的搜索结果分页逻辑 - Search results pagination logic in PHP

我正在开发一个搜索网站，它将使用户能够搜索他们在城市中最喜欢的餐馆。我的数据库设置和我的搜索页面设置。我还能得到返回的搜索结果。但是，我在分页逻辑方面遇到了麻烦，无法获得结果。我怀疑MySQL查询需要调整，但我在什么变化是一个损失。

下面是我的Search.php代码:

<title>Results</title> <link href='./style.css' type='text/css' />
<style>b,p {
    font-family: 'Segoe UI Web Light', 'Segoe UI Light', 'Segoe UI Web Regular', 'Segoe       UI', 'Segoe UI Symbol', 'Helvetica Neue',
Arial; color:#000; font-size:18px;
}
body { font-family: 'Segoe UI  Light', 'Segoe UI Light', 'Segoe UI Web
Regular', 'Segoe UI', 'Segoe UI Symbol', 'Helvetica Neue', Arial;
color:#000; font-size:22px;
} a { text-decoration:none; color:#47a3da} a:hover {
text-decoration:underline; color:black }
* { margin:0px; padding:0px }
ol.timeline   { list-style:none}ol.timeline li{
position:relative;border-bottom:2px #dedede dashed; padding:12px;
}ol.timeline li:first-child{}     .morebox    {   font-weight:light;
  color:#333333;  text-align:center;  border:solid 1px #333333;
  padding:9px;    margin-top:9px;     margin-bottom:9px;
  -moz-border-radius: 7px;-webkit-border-radius: 7px;     }   .morebox a{
color:#333333; text-decoration:none}  .morebox a:hover{ color:#00688B;
text-decoration:none}
#container{margin-left:55px; width:780px } </style>
<?php
$start = 0; $per_page = 4;
if(!isset($_POST['query'])) {
$page = 1;
}  
else {
$page = $_POST['query'];
}
if($page<=1) {
$start = 0; 
}
else {
$start = $page * $per_page - $per_page;
}
####### databse connection  ########   $con = mysql_connect("localhost", "database", "password");
mysql_select_db("database", $con) or die("no"); $sql = "SELECT
hotelname,address,comments,preview,cuisname FROM database.Hotel INNER
JOIN HotelCuisine ON Hotel.id = HotelCuisine.hotel_id INNER JOIN
Cuisine ON Cuisine.id = HotelCuisine.cuisine_id WHERE keywords LIKE
'%$page%'";
$num_rows = mysql_num_rows(mysql_query($sql)); $num_pages =
ceil($num_rows / $per_page); $sql .= " LIMIT $start, $per_page";
?>
<body>
<?php
//####### Fetch Results From Table ########
$result = mysql_query($sql); while($row = mysql_fetch_array($result))
{ $hotelname = $result['hotelname'];  $address = $result['address']; 
$comments = $result['comments'];  $preview = $result['preview']; 
?>
<?php
####### echo the result from table ########
echo "<br>Name: <b>$hotelname</b><br>";  echo "Location:
$address<br>";  echo "$comments<br>"; echo "<a
href=$url>$preview</a><br>"; 
}
?>
</body>
<?php
####### Math OF +1 and -1 for the page ########
$prev = $page - 1; $next = $page + 1;
echo"<hr>"; echo "<a href='?page=$prev'>prev</a> "; echo " <a
href='?page=$next'>next</a> ";
?>

首先，您确定您粘贴在这里的代码是回回$sql时的代码吗?根据我的理解，查询应该是

SELECT hotelname,address,comments,preview,cuisname FROM database.Hotel INNER JOIN HotelCuisine ON Hotel.id = HotelCuisine.hotel_id INNER JOIN Cuisine ON Cuisine.id = HotelCuisine.cuisine_id WHERE keywords LIKE '%biryani%' LIMIT 0, 4

这里0是结果的起始索引，4是返回的行数。

同样，您的代码在检索行数时效率不高。这个可以优化。

如果我要实现它，我会使用下面的逻辑:

<?php
$start = 0; 
$per_page = 4;
if(!isset($_POST['page'])) {
    $page = 1;
} else {
    $page = intval($_POST['page']);
}

####### databse connection  ########   
$con = mysql_connect("localhost", "database", "password");
mysql_select_db("database", $con) or die("no"); 
$sql = "SELECT COUNT(1) AS num_of_rows FROM database.Hotel INNER
        JOIN HotelCuisine ON Hotel.id = HotelCuisine.hotel_id INNER JOIN
        Cuisine ON Cuisine.id = HotelCuisine.cuisine_id WHERE keywords LIKE
        '%$_POST['query']%'";
$num_rows = mysql_fetch_array(mysql_query($sql))["num_of_rows"];
$num_pages = ceil($num_rows / $per_page);
$sql = "SELECT hotelname,address,comments,preview,cuisname FROM database.Hotel INNER
        JOIN HotelCuisine ON Hotel.id = HotelCuisine.hotel_id INNER JOIN
        Cuisine ON Cuisine.id = HotelCuisine.cuisine_id WHERE keywords LIKE
        '%$_POST['query']%' LIMIT ".($page-1)*$per_page.",".$per_page;
$result = mysql_query($sql);
?>
<body>
<?php
//####### Fetch Results From Table ########
while($row = mysql_fetch_array($result)){ 
    $hotelname = $result['hotelname'];  
    $address = $result['address']; 
    $comments = $result['comments'];  
    $preview = $result['preview']; 
    ####### echo the result from table ########
    echo "<br>Name: <b>$hotelname</b><br>";  
    echo "Location: $address<br>";  
    echo "$comments<br>"; 
    echo "<a href=$url>$preview</a><br>"; 
}
####### Math OF +1 and -1 for the page ########
$prev = $page - 1; 
$next = $page + 1;
echo"<hr>"; 
echo "<a href='?page=$prev'>prev</a> "; 
echo " <a href='?page=$next'>next</a> ";
?>
</body>

我再次建议你在命令行或phpMyAdmin中运行$sql，首先检查查询是否正确。