Because although the correct surname is 'Dare' your sql is using 'Dare"'. The next record to 'Dare"' will be 'Ebert', the " makes no difference. The previous record to 'Dare' using the sql  < 'Dare"' will be 'Dare' so it becomes stuck

Looking at the code you posted Re^6: CGI Action call

#Last record Tested Good
       
$stmt = "SELECT * FROM ( 
   SELECT * FROM users 
   WHERE $searchfield <= ? AND $searchfield >= ? 
   ORDER BY $searchfield DESC LIMIT 1 ) sub
ORDER BY $searchfield ASC"; 

#example lastname 
   SELECT * FROM ( 
   SELECT * FROM users 
   WHERE lastname >='z%' AND lastname >= 'a%'
   ORDER BY user_id DESC LIMIT 1 ) sub
   ORDER BY lastname ASC #Works fine hard coded
[download]

The hard coded version has a mistake with >='z%' which should be <= as in the $stmt. Correcting that mistake would give you

   SELECT * FROM ( 
   SELECT * FROM users 
   WHERE lastname <='z%' AND lastname >= 'a%'
   ORDER BY user_id DESC LIMIT 1 ) sub
   ORDER BY lastname ASC
[download]

But, if you think that works correctly then add the name 'Ryan Zimmerman' to you user table and try navigating to last record. Then try the simpler

SELECT * FROM users 
ORDER BY lastname DESC
LIMIT 1
[download]

Whilst this is SQL and not perl, applying the KISS_principle to both makes bugs more easily tracked down.I suspect there are more depending on how you are persisting the sort order between cgi calls

"depending on how you are persisting the sort order between cgi calls" I dropped that. Slowed down responses and no gain

Thanks

Added Zimmerman and it returned Worley, the last record before Zimmerman in lastname field

<code> SELECT * FROM users ORDER BY business ASC LIMIT 1 SELECT * FROM users ORDER BY business DESC LIMIT 1

Wisdom of the monks

Based on record suggestion with Zimmerman I added multiple records with same last name.

The previous and next hit the first one then skip the rest. Tried concatenating other fields but that did not work. Nothing I looked at preserved the order.

Am looking into cursors and stored procedures or stored functions.

Sending the unique record id to the procedure which would return the unique record number of the next record the cursor was pointing to after a fetch would be used to load that record into the form.

Update or insert would not involve the SP. That would only be called when scrolling

This is continent on finding out if the curso/fetch honors the ORDER since it only recognizes moving cursor "forward".

I anticipated this problem when I posted Re^3: CGI Action call.

Am looking into cursors and stored procedures or stored functions.

Not sure you need that complexity, you probably just need to use an OFFSET in your LIMIT statement. If you describe how you are persisting the sort order now that you are not using ALTER TABLE ORDER BY I may be able to suggest a solution.

poj

Have you ever seen this from My SQL manual?>

HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
[download]

GAD!!!Works perfectly

HANDLER users OPEN 
HANDLER users READ business last
[download]