PHP分页: page 2 shows same page 1 rows if new data added

Question

如果有人尝试打开下一页，我正在尝试让我自己的分页系统忽略新数据:

示例:

我有这张 table :

1. apple
2. orange 
3. banana
4. burger
5. pizza 
6. spaghetti

我每页只显示 3 行。

mysql查询:

// $page = 0 for first page
$sql = "SELECT * FROM mytable ORDER BY id DESC LIMIT $page, 3";

结果是这样的

第 1 页:

6. spaghetti
5. pizza
4. burger

第 2 页:

3. banana
2. orange
1. apple

假设现在我位于第 1 页，其他人添加了 3 个新行:

water
juice
coffee

现在会发生的情况是，如果我转到第 #2 页，我将看到重复的页面!

现在第 2 页将是:

6. spaghetti
5. pizza
4. burger

原因是第 1 页显示了新数据:

 9. water
 8. juice
 7. coffee

如果用户没有刷新第一页而只想查看第二页，我该如何阻止这种情况发生？

这是一个问题，尤其是当你试图让你的分页看起来像 Twitter 时!

Answer 1

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM mytable";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
$sql = "SELECT * FROM mytable ORDER BY id DESC LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data
   echo $list['id'] . " : " . $list['number'] . "<br />";
} // end while

/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for

// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>