Limiting the number of rows pulled from sql database and organizing into pages

Question

Currently, I have a page that lists expenses submitted by my staff. The problem is that it pulls everyyyy row from this table of submitted expenses... So it takes forever to load the page (we have data going back to 2006!!!) I would clear the table, but due to our policies we need to maintain a record of 10 years worth..

In any case, I want this page to list 25 entries, and want to have the option of having pages like <<1|2|3|4|5>> of which each will display 25 entries. The PHP code relevant to discussion is:

<?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++) {
?>

I've found this resource helpful, but does not meet my needs: Need to pull last x number of rows based on date

Thanks,

TK

take a look at this http://stackoverflow.com/questions/3799193/mysql-data-best-way-to-implement-paging — Riz, Mar 07 '14 at 03:43
Pagination is just what the doctor orders. Have a look also at => http://www.catchmyfame.com/2014/02/06/php-pagination-class-version-3/ there's a few demos on that page which may interest you. Do consider switching to `mysqli_*` or PDO also ;-) — Funk Forty Niner, Mar 07 '14 at 03:45

Logan Wayne · Answer 1 · 2014-03-07T07:14:05.037

Your talking about pagination. Do this :

<?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>';
    ?>

And please use mysqli instead of its predecessor, mysql. You also might want to consider having a filter/search function in your system. Or categorized them by year.

Limiting the number of rows pulled from sql database and organizing into pages

1 Answers1