"next" link question

All,

I have a problem that has proven to be quite difficult, yet it seems fairly
simple. Hopefully someone here can help - the solution I have come up with
isn't what I'd consider good programming, so I'm looking for a better way of
doing this.

I basically am bringing in records from a MySQL DB, and need my page to flip
through each record using a 'next' link. The problem is that I have no way
of incrementing the next ID to query, as the IDs are not in order (we're
querying by category along with the main ID, so it spreads the return result
IDs apart).

Currently, the only way I've found to do this is to query the DB based on a
variable (passed in a query string) that stores an array of the current
record. So, insde my fetch_array loop, I'm saying

item[] = itemFromDB("itemID")

I'm then keeping track of the index we're at using $i, and passing that in a
query string something liek this:

<a href = "filename?i=$i>

and am incrementing i again, and re-running the query. So it's essentially
dual arrays that I'm tracking to know where we're at in the list of records.

Is there a better solution to this I'm missing?

What I like to do for things like this is run the query once, store the
result in the session, then just pull records out of that as needed.
Not only does it only require one hit on the database, but it prevents
people from tampering with the URL and seeing something that they
shouldn't. Here's how it might work:

At the top of the page, see if the result is already stored in the
session. If not, run the query for all the records that the user is
allowed to see (assuming there aren't /too/ many). Store this result
in a session variable. Set $i = 0. If the result is already in the
session, just pull it out and set $i = $_GET['i].

Grab the data from row number $i and display it. On that page, you can
easily have a link to the next record: filename.php?i=($i+1) or the
previous record ($i-1) or any arbitrary number of records forward
($i+$x) or backward ($i-$x). Of course you should check that
$_GET['i'] is a valid row number for the result.

This also works well when you want to setup paging or sorting --
especially for queries that might take a long time to run.

I have a photo album site that I do this on. What I do hits the database on
each page load, but it works quite well for me.

The concept is just using the LIMIT function of SQL to only request one (or
more) records from the set your query finds.

Here is one of my SQL statements:

SELECT albumid FROM albums ORDER BY timestamp DESC LIMIT $start_album,
$num_albums

$start_album signifies what record to start at, and $num_albums is the total
results to return. On my site, I'm showing 15 thumbnails at a time so a
real example would be:

SELECT albumid FROM albums ORDER BY timestamp DESC LIMIT 45, 15

That starts at record 45 and returns 15 results.

I use the same logic for single picture displays... only it ends up
something like this:

SELECT picid FROM pics ORDER BY timestamp DESC LIMIT 12, 1

That gets the info for the 12th picture in the series (starts at zero
remember) and only returns that one result.

You can see it live here if you like: http://www.dvigroup.net/album/

A page example of the URL is here:
http://www.dvigroup.net/album/set.ph...=284&setpage=2

In my build_page_stuff function, I validate the page number sent - which is
pretty easy. People can type in whatever they want up there and can't mess
it up. Try to break it... (if you succeed, let me know please, but I'm
pretty sure it's bulletproof). At worst, they get put on page 1. I do that
with intval(). That simply turns any text input into a zero effectively.
Then a quick less than zero check and a max number check makes sure that
number in in the right range.

Good luck.


This is the complete function that I use to make my previous and next links:
---------------
function
build_page_stuff_array($page,$pic_count,$album_cou nt,$sourceref,$albumid) {
if ($sourceref == "album") { $extraurl = "albumid=$albumid&amp;"; }

# check for valid page var
$page = intval($page);
if ($page < 1) { $page = 1; }
$max_page = intval($album_count / $pic_count) + 1;
if ($page > $max_page) { $page = $max_page; }

# make pre and next links
$page_pre = $page - 1;
if ($page_pre < 1) { $page_pre = 0; }
$page_next = $page + 1;
if ($page_next > $max_page) { $page_next = 0; }

if ($page_pre > 0) { $page_pre_link =
<<<END
<a href="$sourceref.php?{$extraurl}page=$page_pre"><i mg id="img_pre"
src="gfx/prev.gif" alt="prev" /></a>
END;
}
else { $page_pre_link =
<<<END
<img id="img_pre" src="gfx/prev_gray.gif" alt="prev" />
END;
}
if ($page_next > 1) { $page_next_link =
<<<END
<a href="$sourceref.php?{$extraurl}page=$page_next">< img id="img_next"
src="gfx/next.gif" alt="next" /></a>
END;
}
else { $page_next_link =
<<<END
<img id="img_next" src="gfx/next_gray.gif" alt="next" />
END;
}

$array_page_stuff = array('page' => $page, 'max_page' => $max_page,
'page_pre' => $page_pre, 'page_next' => $page_next, 'page_pre_link' =>
$page_pre_link, 'page_next_link' => $page_next_link);

return $array_page_stuff;
}
---------------

Here is the code that calls it:

$pic_count = 15;
$album_count = get_album_count();
$sourceref = "index";
$array_page_stuff =
build_page_stuff_array($page,$pic_count,$album_cou nt,$sourceref,$albumid);


For a single image page:

$pic_count = 1;
$album_count = get_pic_count($albumid);
$sourceref = "detail";
$array_page_stuff =
build_page_stuff_array($page,$pic_count,$album_cou nt,$sourceref,$albumid);

--
Shawn Wilson