The alter table MyTable order by MyField works in PHP Admin and is in the MySQL docs as well as testing and explanation in Work Log Wl#3681 Report BUG#24562

MySQL Docs: ALTER TABLE tbl_name [alter_specification ORDER BY col_name , col_name ...

My question remains: Why it works in PHP MyAdmin and not in my program?????

Best regards

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.

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.

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