Re^11: CGI Action call

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 \" ?

Comment on Re^11: CGI Action call Select or Download Code

Replies are listed 'Best First'.
Re^12: CGI Action call by poj (Abbot) on Mar 29, 2018 at 07:04 UTC
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]
Re^13: CGI Action call by tultalk (Monk) on Mar 29, 2018 at 13:53 UTC
Thanks That query param contains only Dare, not Dare\". Removed the calls to alter table and it works Ok and faster except for the same problem with navigating to previous record I copy the SQL from the DBI which failed and past into php MyAdmin SQL and it works fine. Do not understand why the difference	[reply]
Re^14: CGI Action call by poj (Abbot) on Mar 29, 2018 at 14:08 UTC
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	[reply] [d/l] [select]
Re^15: CGI Action call by tultalk (Monk) on Mar 29, 2018 at 16:11 UTC
Re^16: CGI Action call by poj (Abbot) on Mar 29, 2018 at 16:35 UTC
Some notes below your chosen depth have not been shown here