Navigating records with PREV/NEXT

ahinkley

I am working on a page that returns text blobs from a database. Each blob is placed in a different row in a table. Since the text blobs can be fairly large, I want to have PREV / NEXT links to flip between the text blobs.

I have a current solution ($query), utilizing a self join based on the id field, that works for everything but the first and last entries. I need to figure out how to make it work for all entries.

Heres my current code: (db table in question is board)

$result = mysql_query("SELECT * FROM board order by id desc",$db);
 printf("<table>");
while ($myrow = mysql_fetch_array($result)) {

$query = "select my.*, min(next.id) as next_id, max(prev.id) as prev_id from board as my, board as next, board as prev where my.id = $myrow[id] and prev.id <my.id and next.id > my.id group by my.id";

$result1 = mysql_query($query);
$data = mysql_fetch_array($result1);

 printf("<a name=%s></a>", $myrow["id"]);
 printf("<tr><td>%s", $myrow["title"], "</td>");
 printf("<td><a href=#%s>prev</a> :: <a href=#%s>next</a></td></tr>", $data["prev_id"], $data["next_id"]);
 printf("<tr><td>%s<br>\n", $myrow["boardtext"], "</td></tr>");
}
 printf("</table>");

Any help would be appreciated!

-Andrew

Trojan

if you want to know how to use the prev/next buttons then go to this website. i had the same problem and someone from here helped me out.

its not easy the tutorial but will help u out in understanding

http://www.evolt.org/article/Multiple_Pages_With_PHP/
hope it helps

ahinkley

Thanks for the reply, but this is not what I am really looking for.

That page basically shows a way to retrieve a select number of entries from a database, so each click of the NEXT button would retreive the next 10 records, etc.

In my situation, I am retreiving all entries from the database, printing them in one long list down the page. I just want a way to skip between the entries, one at a time.

It would be as simple as just pulling the id from the database, and incrementing or decrementing it as needed. This however will break when a record is deleted from the database though. I grab the id from an autonumber field, so if I have entries 1,2,3,4,5 it works fine. If I delete #3, the database now holds 1,2,4,5 , and the NEXT button will not work on id=2, the PREV will not work on id=4.

Faced with this problem, I received some helpful advice that a self join could allow me to compare the id field to itself within the same table, and thus figure out which ids were before and after a specific id. This is where I am at currently, as this works great for everything BUT the first and last entries returned.

I am imagining some sort of conditional test to determine if an id is the first or last entry in the database, I just have yet to visualize what this conditional is...

-Andrew