php - 获取结果时排序不适用于 LIMIT

Question

我目前正在开发一个基本上基于搜索的网站。最终用户可以搜索当前在网站上注册的成员(member)。注册用户可享受 Golden、Silver 和 Basic 三种成员(member)套餐。我有一个分页，当我尝试根据包对结果进行排序时，我在不同的页面上得到了重复的结果。例如，如果我在第 3 页上找到一个用户，他也可能出现在第 6 页上。我不知道我做错了什么。任何帮助将不胜感激。我粘贴在我的 MySQL 查询下面，该查询从数据库中获取结果。

SELECT * FROM users 
LEFT JOIN prof_info 
ON users.id = prof_info.user_id 
WHERE (prof_info.work_country = 'Indonesia' 
       OR (prof_info.work_country ='' AND users.country = 'Indonesia'))
       AND users.firstname !=''
ORDER BY users.membership DESC 
LIMIT 0, 10

成员(member)套餐有以下数据库条目:

黄金=2，白银=1，基本=0。我希望能够在搜索结果中显示 Golden 成员(member)，而不是 silver 和 afterwords 基本成员(member)。

创建分页的代码是

<?php
if (isset($_GET["page"])) { $current_page  = $_GET["page"]; } else { $current_page=1; };

$limit = 1; // number of results per page
$start_from = ($current_page-1) * $limit;
if(isset($total_results)){
$nav  = '';
$skip_links1 = 1;
$skip_links2 = 1;
for($page = 1; $page <= $total_pages; $page++)
{
   if ($page == $current_page)
   {
      $nav .= " $page "; // no need to create a link to current page

   }
   else
   {
       if(($page > 2) && ($page < $total_pages-2) && ($page > $current_page + 2)){//number of pages exceeding 5
       if($skip_links1 == 1)
        $nav .= " ... ";
        $skip_links1 =0;
       }elseif($page >2 && $page < $current_page -2 ){
           if($skip_links2 == 1)
           $nav .= " ... ";
           $skip_links2=0;
      }else{
          $nav .= "<a href='doctors.php?page=".$page."&country=".$_GET['country']."&city=".$_GET['city']."&speciality=".$_GET['speciality']."&name=".$_GET['name']."'>".$page."</a> ";
     }
  }
}

if ($current_page > 1)
{
   $page  = $current_page - 1;
   $prev  = " <a href='doctors.php?page=".$page."&country=".$_GET['country']."&city=".$_GET['city']."&speciality=".$_GET['speciality']."&name=".$_GET['name']."'><</a> ";

   $first = " <a href='doctors.php?page=1&country=".$_GET['country']."&city=".$_GET['city']."&speciality=".$_GET['speciality']."&name=".$_GET['name']."'><<</a> ";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}

if ($current_page < $total_pages)
{
   $page = $current_page + 1;
   $next = " <a href='doctors.php?page=".$page."&country=".$_GET['country']."&city=".$_GET['city']."&speciality=".$_GET['speciality']."&name=".$_GET['name']."'>></a> ";

   $last = " <a href='doctors.php?page=".$total_pages."&country=".$_GET['country']."&city=".$_GET['city']."&speciality=".$_GET['speciality']."&name=".$_GET['name']."'>>></a> ";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
} ?>
<div id="tableNav">

<?php
echo "<center>".$first . $prev . $nav . $next . $last."</center>";

?>
</div>
<?php
        }//end of if(isset($total_results))

?>

Answer 1

首先将 LEFT JOIN 换成 INNER JOIN 因为你有 on 子句。其次，您的表中似乎有重复项。每个用户只能获得一种成员资格类型，例如使用此查询获得最高的成员资格类型

SELECT 
    *,MAX(users.membership)
FROM
    users
       INNER JOIN
    prof_info ON users.id = prof_info.user_id
WHERE
    (prof_info.work_country = 'Indonesia'
        OR (prof_info.work_country = ''
        AND users.country = 'Indonesia'))
        AND users.firstname != ''
GROUP by users.id
ORDER BY users.membership DESC
LIMIT 0 , 10