query question

[Anon]

Hi, here is a strange question

i have a page that selects box numbers there are 2 links at the bottom forward and back, the forward works like this:

$query = "SELECT * FROM box WHERE custno = '$custno' AND boxno > '$forward1'";

this is fine the problem is in the back button, the code is:

$query = "SELECT * FROM box WHERE custno = '$custno' AND boxno < '$back1'";

however it goes to the first number so if i was on box 12 and clicked back it would go to box 1 not box 11, is there any code that will select the boxno closest to this one.

thanks for you help, Martin

[Anon]

It's ok i've solved it, ordering it via boxno and then using DESC

thanks for your help

[[deleted]]

Your definition of forward and back is wrong.
That fact that the 'forward' works is pure luck.

If you are on item 5, then the next item in your database is probably item 6, and you probably inserted item 6 right after inserting item 5, so they are stored in the database in sequence. However, this does not have to be that way.
The first record in the database's table may well be item 5464.

You see this effect already when you select the previous item. The database stored item 1 before item 5, so when you select an item where the itemnumber is less than 5, item 1 is a match.

What you should do is define exactly which item number you want, or describe which item number qualifies as the next or previous.

SELECT FROM table WHERE itemno = 4;
or
SELECT FROM table WHERE itemno = 6;

If you have gaps in your item numbering, you can do the next best thing.
This means describe which item numbers qualify.
If you want the next itemno, you are looking for the smallest itemno that is larger than the current.
SELECT min(itemno) FROM table WHERE itemno > 5;
and for the 'back'
SELECT max(itemno) FROM table WHERE itemno < 5;