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

Maybe some other instance of your program is changing the sort order at the same time?

Even the MySQL documentation tells us that this ordering will not respect changes to the table.

You shouldn't rely on such a feature but use the order by clause in your SELECT statements.

Also, you should show some representative examples of data in your table, and the exact SELECT statements you run, and the exact output you get, and the exact output you expect. It makes it relatively hard for us to slog through the reams of code you post, half of which is commented-out debris from other attempts by you.

Please take some time to prepare a short, self-contained example that we can run to reproduce and maybe fix your problem. It should be shorter than 20 lines.

Replies are listed 'Best First'.
Re^10: CGI Action call
by tultalk (Monk) on Mar 28, 2018 at 00:10 UTC

    Tracked it down for now. I have a variable storing a sort index (0,1 or 2). It is correct after I search a name which is supposed to establish the the order. It does. When I execute the code to get the next record the sort index is 0 while for lastname it was 1 before entering the sub for navigating. The $sortIndex

    $sortindex is declared here: use vars qw($dataupdatemessage $dataupdatefilemessage $LoggedOnId $sortindex ); It should persist. Searched all instances and see nothing that would change it.

[reply]
      It should persist.

      Do you mean it should persist between search_Records() and navigate_Records(). How, on the client in javascript ?

      poj
[reply]
[d/l]
[select]
Re^10: CGI Action call
by tultalk (Monk) on Mar 27, 2018 at 21:13 UTC

    Thanks for comments

    You say "Even the MySQL documentation tells us that this ordering will not respect changes to the table."

    I am aware of the effects of inserts, deletes and even mods to data IIRC.

    I tested that with an insert and it indeed broke the order which only requires re-invoking the alter table command

    I am only doing a select and am re-invoking the alter table after enetering the select subroutine. It does not help.

    So in SQL Sceibble, lets try the alter table order by name and change the SQL statements. That should demonstrate any problem without my trying to extract "20 lines of code" from my program to reproduce the result.

    You pointed to the problem earlier in you post in this thread. Using your already existing example should demonstrate the problem.

    You state "Also, your SQL seems faulty to me as the "previous record" seems to skip users:"

    create table users ( user_id integer not null primary key, name varcha
+r(16) );

insert into users values (1,'Fred');
insert into users values (2,'Barney');
insert into users values (3,'Wilma');
insert into users values (4,'Betty');
insert into users values (5,'Pebbles');


SELECT *
FROM usersI
WHERE user_id < (SELECT MAX(user_id)
                 FROM users WHERE user_id < 4) 
ORDER BY user_id DESC LIMIT 1

    [download]

    I just SQL scribbled with your scribble and 

    SELECT * 
FROM users WHERE user_id >= (SELECT MIN(user_id) 
                             FROM users WHERE user_id > 4) 
ORDER BY user_id ASC LIMIT 1

    [download]

    with 4 Returned "Pebbles" and with "2" returned "Wilma". That statement is working correctly so I am assuming my other statement will also work correctly.

    So are we saying that something in my program is de-ordering? I am not executing anything that would do that as far as I know. I will look further.

    Where can I get SQL Scribble? When I searched on it I only saw ref to MS-SQL.

    Best regards

[reply]
[d/l]
[select]

      Help if I searched Fiddle and not Scribble!!!!!

[reply]

        Found it. Tested my other statement. Works fine.

        Now on to alter table

[reply]

      FIXED. Appears to work perfectly. I had some real screw ups in the javascript Took all this time to hunt the problems down and fix them.

      Thanks for help on this issue

[reply]

      Spoke too soon

      Testing next and previous record calls

      When ORDER is set to user_id scrolling works fine both ways.

      When ORDER is set to lastname, next works fine but previous is stuck on current record

      No code posted as error logs and DBI-trace informs more thoroughly(In my opinion) in this case than "slogging" though code (More than 20 lines).

      Below are error logs and accompanying DBI->trace showing the steps through and the statement binding. looks to me exactly as it should except the JSON result from <-.

      Please note the JSON at the end of each error log. The Error Log -> moved to the next record which is the lastname = "Ebert" record while the Error Log <- previous is stuck on the lastname = "Dare" record. Repeated calls <- just loads the same record

      <"dare" is not the first record in the lastname order as it is preceded by 7 records and followed by more than 50 records. (alphabetical order)

      As I said before the -> statement and <- statement both work as expected in php Admin SQL with the ALTER TABLE users ORDER BY lastname ASC issued before the lastname tests.

      The DBI-Trace also shows the alter statement issued as it should be by my program

      parse_params statement alter table users order by lastname asc
 <- dbd_st_execute returning imp_sth->row_num 60
    <- execute= 60 at update_tables.cgi line 511 via  at update_tables
+.cgi line 321

      [download]

      Line 321 is the call to restoreOrder($sortindex) to assure the table is sorted in coordination with the search criteria

      Error Log Move Pointer ->

[Wed Mar 28 15:18:59 2018] update_tables.cgi: Entered update_tables.cg
+i Open Database return First Record with action: = 'navigate_records'
+ at update_tables.cgi line 290.

[Wed Mar 28 15:18:59 2018] update_tables.cgi: search parameter = '2' a
+t update_tables.cgi line 291.

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

[Wed Mar 28 15:18:59 2018] update_tables.cgi: sortindex = '1' at updat
+e_tables.cgi line 299.

[Wed Mar 28 15:18:59 2018] update_tables.cgi: searchfield = 'lastname'
+ sortindex = '1' at update_tables.cgi line 319.

[Wed Mar 28 15:18:59 2018] update_tables.cgi: searchIndex =   '1' at u
+pdate_tables.cgi line 489.

[Wed Mar 28 15:18:59 2018] update_tables.cgi: in restoreOrder index = 
+'1' at update_tables.cgi line 503.

[Wed Mar 28 15:18:59 2018] update_tables.cgi: statement =  'SELECT * F
+ROM users WHERE lastname >= (SELECT MIN(lastname) FROM users WHERE la
+stname > ?) ORDER BY lastname ASC LIMIT 1' at update_tables.cgi line 
+362.

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

[Wed Mar 28 15:18:59 2018] update_tables.cgi: navigate statement = 'SE
+LECT * FROM users WHERE lastname >= (SELECT MIN(lastname) FROM users 
+WHERE lastname > ?) ORDER BY lastname ASC LIMIT 1' at update_tables.c
+gi line 377.

[Wed Mar 28 15:18:59 2018] update_tables.cgi: navResult = 'HASH(0x3115
+b60)' at update_tables.cgi line 381.

[Wed Mar 28 15:18:59 2018] update_tables.cgi: count = '1' at update_ta
+bles.cgi line 385.

[Wed Mar 28 15:18:59 2018] update_tables.cgi: count = '1' at update_ta
+bles.cgi line 396.

Finished print {"DD":"1973-11-30","DP":"1974-10-02","MD":"120.00","MJ"
+:"1990-03-29","address1":"79257 Branson Corners Suite 41","address2":
+"Suite 295","business":"Block, Bernier and Sporer","city":"Port Randa
+lport","comments":"Mollitia odit quis ipsum quo et voluptate libero. 
+Accusamus vel culpa magni ipsam adipisci.\nUt quas esse est ea. Deser
+unt rerum quia quo et. Enim et totam sapiente et non a saepe.","email
+":"yadams@gutmann.org","forename":"Christophe","id":91,"lastname":"Eb
+ert","password":"44 wy","phone_cell":"352-996-7500x6","phone_home":"0
+84-688-5902","pin":"jbwmi","position":4,"state":"WY","user_id":37,"us
+ername":"buckridge.myra","zip":"26974-0929"} at update_tables.cgi lin
+e 402.


DBI  ->

parse_params statement SELECT * FROM users WHERE lastname >= (SELECT M
+IN(lastname) FROM users WHERE lastname > ?) ORDER BY lastname ASC LIM
+IT 1
Binding parameters: SELECT * FROM users WHERE lastname >= (SELECT MIN(
+lastname) FROM users WHERE lastname > 'Dare\"') ORDER BY lastname ASC
+ LIMIT 1

      [download]
      Error Log File Move Pointer <-

[Wed Mar 28 14:56:04 2018] update_tables.cgi: Entered update_tables.cg
+i Open Database return First Record with action: = 'navigate_records'
+ at update_tables.cgi line 290.

[Wed Mar 28 14:56:04 2018] update_tables.cgi: search parameter = '1' a
+t update_tables.cgi line 291.

[Wed Mar 28 14:56:04 2018] update_tables.cgi: rowpointer = 'Dare"' at 
+update_tables.cgi line 292.


[Wed Mar 28 14:56:04 2018] update_tables.cgi: sortindex = '1' at updat
+e_tables.cgi line 299.

[Wed Mar 28 14:56:04 2018] update_tables.cgi: searchfield = 'lastname'
+ sortindex = '1' at update_tables.cgi line 319.

[Wed Mar 28 14:56:04 2018] update_tables.cgi: searchIndex =   '1' at u
+pdate_tables.cgi line 489.

[Wed Mar 28 14:56:04 2018] update_tables.cgi: in restoreOrder index = 
+'1' at update_tables.cgi line 503.

[Wed Mar 28 14:56:05 2018] update_tables.cgi: statement =  'SELECT * F
+ROM users WHERE lastname <= (SELECT MAX(lastname) FROM users WHERE la
+stname < ?) ORDER BY lastname DESC LIMIT 1' at update_tables.cgi line
+ 362.

[Wed Mar 28 14:56:05 2018] update_tables.cgi: kind = '1' rowpointer = 
+'Dare"' at update_tables.cgi line 373.

[Wed Mar 28 14:56:05 2018] update_tables.cgi: navigate statement = 'SE
+LECT * FROM users WHERE lastname <= (SELECT MAX(lastname) FROM users 
+WHERE lastname < ?) ORDER BY lastname DESC LIMIT 1' at update_tables.
+cgi line 377.

[Wed Mar 28 14:56:05 2018] update_tables.cgi: navResult = 'HASH(0x2d6e
+b60)' at update_tables.cgi line 381.

[Wed Mar 28 14:56:05 2018] update_tables.cgi: count = '1' at update_ta
+bles.cgi line 385.

[Wed Mar 28 14:56:05 2018] update_tables.cgi: count = '1' at update_ta
+bles.cgi line 396.

[Wed Mar 28 14:56:05 2018] update_tables.cgi: Finished print {"DD":"19
+74-03-17","DP":"1986-11-10","MD":"120.00","MJ":"1997-05-31","address1
+":"839 Baumbach Lane","address2":"Apt. 701","business":"Runolfsdottir
+, Mueller and Moe","city":"Gloverton","comments":"Qui molestias ipsum
+ non velit deserunt. Et qui similique expedita sunt explicabo qui. Vo
+luptatem corporis cupiditate quia quis neque sit ut.","email":"bergst
+rom.cindy@pollich.biz","forename":"Vickie","id":8,"lastname":"Dare","
+password":"30 eq","phone_cell":"+45(0)20928653","phone_home":"385.259
+.7558","pin":"sbwmn","position":4,"state":"ID","user_id":12,"username
+":"jaeden68","zip":"08973-6995"} at update_tables.cgi line 402.

DBI <-

parse_params statement SELECT * FROM users WHERE lastname <= (SELECT M
+AX(lastname) FROM users WHERE lastname < ?) ORDER BY lastname DESC LI
+MIT 1
Binding parameters: SELECT * FROM users WHERE lastname <= (SELECT MAX(
+lastname) FROM users WHERE lastname < 'Dare\"') ORDER BY lastname DES
+C LIMIT 1

      [download]

      As an aside, do you know why this unmatched " is there in 'Dare\"' and escaped \" ?

[reply]
[d/l]
[select]
        As an aside, do you know why this unmatched " is there in 'Dare\"' and escaped \" ? 
        [Wed Mar 28 15:18:59 2018] update_tables.cgi: Entered update_tables.cg
+i Open Database return First Record with action: = 'navigate_records'
+ at update_tables.cgi line 290.
[Wed Mar 28 15:18:59 2018] update_tables.cgi: rowpointer = 'Dare"' at 
+update_tables.cgi line 292.

        [download]

        Looks like it comes in on the query param.

        sub navigate_Records {: 
   my $kind = $query->param('kind');
   my $rowpointer = $query->param('searchterm'); 
   my ($sth, $stmt);
   my @row;
   warn("Entered update_tables.cgi Open Database return First Record w
+ith action: = '$action'");   
   warn("Search Parameter = '$kind'");
   warn("rowpointer = '$rowpointer'");   
   ...

        [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom