in reply to Re^15: CGI Action call
in thread CGI Action call

why -> works and <- doesn't

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

poj

Replies are listed 'Best First'.
Re^17: CGI Action call
by tultalk (Monk) on Mar 29, 2018 at 17:32 UTC
    <code> SELECT * FROM users ORDER BY business ASC LIMIT 1 SELECT * FROM users ORDER BY business DESC LIMIT 1

    Wisdom of the monks

[reply]
Re^17: CGI Action call
by tultalk (Monk) on Mar 30, 2018 at 11:02 UTC

    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".

[reply]

      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
[reply]
[d/l]

      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]
[reply]
[d/l]
[select]
        Have you ever seen this from My SQL manual ?
        The HANDLER ... OPEN statement opens a table, making it accessible using subsequent HANDLER ... READ statements. This table object is not shared by other sessions and is not closed until the session calls HANDLER ... CLOSE or the session terminates.
[reply]
Re^17: CGI Action call
by tultalk (Monk) on Mar 29, 2018 at 16:50 UTC

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

[reply]
Re^17: CGI Action call
by tultalk (Monk) on Mar 29, 2018 at 16:59 UTC

    Thanks

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

[reply]

In Section Seekers of Perl Wisdom