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

That query param contains only Dare, not Dare\".

Why then does your log say rowpointer = 'Dare"'

[Wed Mar 28 15:18:59 2018] update_tables.cgi: rowpointer = 'Dare"' at 
003: +update_tables.cgi line 292.

[download]
Removed the calls to alter table

How are you persisting the sort order variable $sortindex between calls to search_Records() and navigate_Records() ?. You appear to be using the same parameter name kind for sort order in search_Records() and movement direction in navigate_Records(). Why not create another parameter for movement direction and then persist the sort order with it's own parameter.

poj

Replies are listed 'Best First'.
Re^15: CGI Action call
by tultalk (Monk) on Mar 29, 2018 at 16:11 UTC

    rowpointer = 'Dare"'

    It does indeed

    In php MyAdmin previous (<-) SQL the \" causes the command to just reload the Dare record. Removing the \" from the SQL allows it to fuction property.

    However, in the next (->) SQL the next record is sent to the client as expected with or without removing the \".

    Very strange

    No \" after Dare

    Found problem buried in javascript. Fixed.

    Thanks. Still doesn't shed light on why -> works and <- doesn't with the \" after the lastname.

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

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

[reply]

        Thanks

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

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

        Wisdom of the monks

[reply]

        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]

In Section Seekers of Perl Wisdom