Re^10: CGI Action call

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

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

Replies are listed 'Best First'.
Re^11: CGI Action call by tultalk (Monk) on Mar 27, 2018 at 21:17 UTC
Help if I searched Fiddle and not Scribble!!!!!	[reply]
Re^12: CGI Action call by tultalk (Monk) on Mar 27, 2018 at 21:30 UTC
Found it. Tested my other statement. Works fine. Now on to alter table	[reply]
Re^11: CGI Action call by tultalk (Monk) on Mar 28, 2018 at 03:09 UTC
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]
Re^11: CGI Action call by tultalk (Monk) on Mar 28, 2018 at 21:53 UTC
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]
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
Re^15: CGI Action call by tultalk (Monk) on Mar 29, 2018 at 16:11 UTC
Some notes below your chosen depth have not been shown here