scriptygoddess

(another non-blog post)
Problem: Let's say you're displaying rows of data from your database, and there's just too many rows for one page. You'd like to show X number of rows of data per page, and then have "next"/"previous" buttons to navigate through the list. Let's also say you want to be able to sort all this data by certain fields, and have that "sort" remembered as you go from page to page (clicking the "next" "previous" buttons).

Solution: (If there's another way to do this without the double query – please let me know)…

<?
//Set this number to the max you want to display per page…
$numToDisplay = 20;
if (isset($_REQUEST['nxtgrp'])) {
$nxtgrp = $_REQUEST['nxtgrp'];
$urlnextgrp = "nxtgrp=".$_REQUEST['nxtgrp'];
$limitTo = "LIMIT ".$_REQUEST['nxtgrp'].",".$numToDisplay;
} else {
$nxtgrp = 0;
$urlnextgrp = "nxtgrp=0";
$limitTo = "LIMIT 0,".$numToDisplay;
}
$sort = "";
$sortby = "";
if (isset($_REQUEST['sortby'])) {
$sort = "ORDER BY ".$_REQUEST['sortby'];
$sortby = "sortby=".$_REQUEST['sortby'];
}
//////————–////
leaving out the code to connect to the database, and select the appropriate table
/////————-/////

$getCount = "SELECT count(*) as count FROM yourTable";
$getCountResult = mysql_query($getCount);
$getCountRow = mysql_fetch_array($getCountResult);
$totalRows = $getCountRow['count'];
?>

<p>
<? if ($nxtgrp > 0) {?>
[ <a href="<? echo $_SERVER['PHP_SELF']; ?>?nxtgrp=<? echo $nxtgrp-$numToDisplay; ?>&<? echo $sortby; ?>"><<previous</a>
]
<? } else {
echo "<font color=\"#999999\">[ <<previous ]</font>";
} ?>
<? echo $nxtgrp+1; ?> –
<? if ($nxtgrp+$numToDisplay > $totalRows) {
echo $totalRows;
} else {
echo $nxtgrp+$numToDisplay;
}
?>
(of <? echo $totalRows; ?>)
<? if (($nxtgrp+$numToDisplay) < $totalRows) { ?>
[ <a href="<? echo $_SERVER['PHP_SELF']; ?>?nxtgrp=<? echo $nxtgrp+$numToDisplay; ?>&<? echo $sortby; ?>">next>></a>
]
<? } else {
echo "<font color=\"#999999\">[ next>> ]</font>";
} ?>
</p>

Then, lets say you have the "header title" in the rows linked to be the one to sort by… so if you have a list of users, and user's data, and you want to be able to have people click on "USERNAME" to sort by that column… do this ("username" is the actual name of the field in the database):

<a href="<? echo $PHP_SELF ?>?sortby=username&<? echo $urlnextgrp; ?>">Username</a>

After you finish your header row, then you do the query for the actual data for this page:

$sql = sprintf("SELECT username, password, access FROM yourTable %s %s", $sort, $limitTo);
$r = mysql_query($sql);
$num = mysql_num_rows($r);
for ($i=0; $i < $num; $i++) {
$row = mysql_fetch_array($r);
<tr>
<td><? echo $row['username']; ?></td>
etc…
</tr>
<? } ?>

So just to put a "face" to all this…Here's your data table kinda looks: (note this is NOT a functioning data display… just wanted to show you what the table that I was working with sort of looks like):

[<<previous] 1-2 (of 5) [next>>]