i have a dynamic html table, generated with php, with data from mariadb
i need an algorithm (preferently in sql, or in php if not) to re-order the rows and submit... like this (where x = checkbox, o = radiobutton):
range dest data
---------------
. . one
x . two
x o three
x . four
. . five
[submit move]
the idea is similar to excel> select rows> move+insert rows... first you select a source RANGE (i did with js, just select first, last, and the range selects itself), then you select an insert destination position, then submit... in this example the result will be
one
five
two
three
four
as you can see the selected range [two..four] has advanced to specified position, shifting the overwriten items to space left by the range, preserving the length but just changing indexes... i imagine some sql commands like
UPDATE my_table SET index=index+$diff WHERE index>=$start AND index<=$end;
seems easy but is not so much (for me at least) since there are many different cases with different range lenghts, positions, etc... i couldn't found any native sql or php function or snippet to do it
please if you have some knowledge or idea about some related algorithm i will be very thankful ... and forgive my bad english xD
due the question popularity xD i tried to find an answer myself... and share it with the hope will be useful to somebody, and hopufully motivate someone else to find a better solution
consider this initial set of elements (only indexes shown), and that we want to move elements from 2 to 4 to position 6... we expect a final result
orig 1 2 3 4 5 6 7 8 9
src 2 3 4
dest 6
final 1 5 6 7 8 2 3 4 9
first thing i did was to calculate the source range length, that is 4 -2 = 2
then saved the source range in a temp table, or by marking elements someway, or by writing down unique id's, etc...
until the indexes could simply be duplicated, imagine instead we take away the source range and left a "hole" of "length +1" elements in the list
2 3 4
^ ^ ^
1 . . . 5 6 7 8 9
then the key is to shift the remaining elements to the opposite side "length +1" positions, can be done by substracting "length" to remaining elements
< < <
1 5 6 7 8 . . . 9
take note the remaining items you shifted is 4, this is the difference between after the last source element and the last destination position... the last destination position is destination + "length" (6 +2 = 8)
in the case the movement is done to the opposite direction (i.e. the source range to their left) all ranges, shifts and operations must be corrected acordingly
now you can set source range to his new position by simply correcting the indices adding "remaining length" (4) to indexes
> > > > 2 3 4
1 5 6 7 8 . . . 9
be aware destination position can't go outside (first) and (last -"length") positions, i.e. (1 and 7) for this case... and if first source index equals destination the list wouldn't change
so to conclude, there 3 cases with slightly different operations:
now you could post a pseudocode snippet below as a complement :) ... thanks