L.S L.S - 26 days ago 5x
PHP Question

Creating a next and previous buttons

I'm trying to do a next and previous button based on records from database but i'm not really sure how to do it and which would be the best way.
The way that i was thinking to do it
would be to have a field in the table named position.
and the query would look like this:

SELECT * FROM t1 WHERE step = 1

then in the php i would have something like this

$step = $step + 1 // for the next button
if($step === 1) {
$step = 1;
} else {
$step = $step - 1;

But if i would delete a record then my position would be out of sync with the math and i won't be able to do + or - on step field because i would find the record.
Which means everytime i would delete something i would need to do a new increment on that field to keep it synced which might later become a problem.

How should i handle it ?


You can add a row number to a query like this:

select @qposn:=@qposn+1 posn, a.* from sometable a, (select @qposn:=0) x

You can select a specific row, in this case the 2nd row, like this:

select * from (
select @qposn:=@qposn+1 posn, a.* from sometable a, (select @qposn:=0) x
) x where posn=2

But be aware that if the data in the table changes the numbering will change also. If someone deletes the first record in the table then the data that used to be row 2 will now be row 1 and so paging may skip or repeat rows.

If your table is relatively small you might consider caching the table in a javascript array and then paging through the array.