php - 限制从 SQL 数据库中提取的行数并组织成页面

Question

目前，我有一个页面列出了我的员工提交的费用。问题是它从提交的费用表中提取每一行...因此加载页面需要很长时间(我们的数据可以追溯到 2006 年!!!)我会清除该表，但由于我们的政策，我们需要保持10年的记录..

无论如何，我希望此页面列出 25 个条目，并且希望可以选择具有像 <<1|2|3|4|5>> 这样的页面，其中每个页面将显示 25 个条目。与讨论相关的 PHP 代码是:

<?php
$forms = array();
if ($checkr[4]==2)
    $dtfq = mysql_query("SELECT `index`,`time`,`status`,`user` FROM `dtf` ORDER BY `index` desc;");
else
    $dtfq = mysql_query("SELECT `index`,`time`,`status`,`user` FROM `dtf` WHERE `user`=".$checkr[0]." ORDER BY `index` desc;");
while($dtf = mysql_fetch_row($dtfq)) {
    $newentry = array(1,$dtf[0],$dtf[1],$dtf[2],$dtf[3]);
    $forms[] = $newentry;
}
if ($checkr[4]==2)
    $crfq = mysql_query("SELECT `index`,`time`,`status`,`user` FROM `crf` ORDER BY `index` desc;");
else
    $crfq = mysql_query("SELECT `index`,`time`,`status`,`user` FROM `crf` WHERE `user`=".$checkr[0]." ORDER BY `index` desc;");
while ($crf = mysql_fetch_row($crfq)) {
    $newentry = array(2,$crf[0],$crf[1],$crf[2],$crf[3]);
    $forms[] = $newentry;
}
for ($i=0; $i<count($forms); $i++) {
    for ($j=0; $j<count($forms); $j++) {
        if ($forms[$j][2]<$forms[$i][2]) {
            $temp = $forms[$j];
            $forms[$j] = $forms[$i];
            $forms[$i] = $temp;
        }
    }
}
for ($i=0; $i<count($forms); $i++) {
?>

我发现此资源很有帮助，但不能满足我的需求:Need to pull last x number of rows based on date

谢谢

传统知识

Answer 1

您谈论的是分页。这样做:

<?php

include 'conn.php'; /* Assume that this has your connection to your DB and put your connection to $con variable */
$dtfq = "SELECT `index`,`time`,`status`,`user` FROM `dtf` ORDER BY `index` DESC";
$result = mysqli_query($con, $dtfq); /* Do the Query */
$count=mysqli_num_rows($result); /* Count the number of rows */

$r = mysqli_fetch_row($result);
$numrows = $r[0];

echo '<b>&nbsp;'.$count.' results found</b>';
$rowsperpage = 25; /* Your request to have 25 entries per page */
$totalpages = ceil($count / $rowsperpage);

if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
      $currentpage = (int) $_GET['currentpage'];
} else {
     $currentpage = 1;
} 

if ($currentpage > $totalpages) {
   $currentpage = $totalpages;
} 
if ($currentpage < 1) {
   $currentpage = 1;
} 

$offset = ($currentpage - 1) * $rowsperpage;

$dtfq = "SELECT `index`,`time`,`status`,`user` FROM `dtf` ORDER BY `index` DESC LIMIT $offset, $rowsperpage";

$result=mysqli_query($con, $dtfq);

/*start of the table*/
echo "<table border='1'>
<tr>
<th>Index</th>
<th>Time</th>
<th>Status</th>
<th>User</th>
</tr>";

/*start of the record display*/
while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['index'] . "</td>";
  echo "<td>" . $row['time'] . "</td>";
  echo "<td>" . $row['status'] . "</td>";
  echo "<td>" . $row['user'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

echo '<table border="0"><tr><td>';

/******  build the pagination links ******/
$range = 2;

if ($currentpage > 1) {
   $prevpage = $currentpage - 1;
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'>Previous</a> ";
} 


for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {

   if (($x > 0) && ($x <= $totalpages)) {
      if ($x == $currentpage) {    
         echo " <font color='#546f3e'><b>$x</b></font> ";
      } else {
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } 
   } 
}                  

if ($currentpage != $totalpages) {
   $nextpage = $currentpage + 1;
     echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>Next</a> ";

} // end if
/****** end build pagination links ******/
echo '</td></tr></table>';
    ?>

并且请使用 mysqli 而不是它的前身 mysql。您可能还需要考虑在系统中添加过滤/搜索功能。或者按年份对它们进行分类。