re-ordering and sorting 4

[nicklieb]

Hi guys,

need a pointer here, on a problem I'm trying to work through.

I have a list of variables that I want to have a priority assigned to, each line has drop down that corresponds to their priority ( ie 1 to ~).

I want to be able to move a variable to any position in the list and then adjust the items according to where that variable was placed.

A   1
B   2
C   3
D   4
E   5    ..etc

now say I want to move E to position 1 as follows

E    1
A    2
B    3
C    4
D    5

Once this sort has been done it then need to update all the database once the sort is complete.

My first thought was a bubble sort, fairly simple I though.

I have a table within Sql that has the ranking positions in them, now I need to update these sets of data. so was logic was as follows:

1. pull data from database and insert into array
2. create a new array looping through the array (created in 1) until I hit my required position.
3. Add the changed reference into the array and then carry out transferring data from the first arrya, ensuring that the reference was not duplicated
4. loop through the built array and re insert into the database.


Abit long winded no?? And the list could be very long upto 1000 lines potentially ( thereby making the script a tad slow)

There must be an easier way to do this? Has anyone had any experience and give me a pointer of a better way to approach this problem.

Many thanks in advance

 

[steven290]

lets say are in position 5(id=24) and want to move to 7

>= version
first update
id orig new
20 1 1
21 2 2
22 3 3
23 4 4
24 5 4
25 6 5
26 7 7
27 8 8
28 9 9

second update
id orig new
20 1 1
21 2 2
22 3 3
23 4 4
24 5 6
25 6 5
26 7 7
27 8 8
28 9 9

> version
first update
id orig new
20 1 1
21 2 2
22 3 3
23 4 4
24 5 5
25 6 5
26 7 7
27 8 8
28 9 9

second update
id orig new
20 1 1
21 2 2
22 3 3
23 4 4
24 5 6
25 6 5
26 7 7
27 8 8
28 9 9

same output

 

[Sheco]

its not the second update that I'm worried about...

Suppose you want to move B to the 4 rank.

Starting with:
A 1
B 2
C 3
D 4
E 5


The first SQL is this:
"UPDATE "&tablename&" set "&sortfield&"="&sortfield&" - 1 where "&sortfield&" > "&origpos&" and "&sortfield&" < "&newpos&";"

Which gives your this:
A 1
B 2
[red]C 2[/red]
D 4
E 5

The second SQL:
"UPDATE "&tablename&" set "&sortfield&"="&newpos&"-1 where "&idfield&"="&idtoupdate&";"


Give you this:
A 1
[red]B 4[/red]
C 2
[blue]D 4[/blue]
E 5

So now both the B and D are in the 4 position.


If you change the first SQL to this:
"UPDATE "&tablename&" set "&sortfield&"="&sortfield&" - 1 where "&sortfield&" > "&origpos&" and "&sortfield&" <[red]=[/red] "&newpos&";"

You get:
A 1
B 2
[red]C 2
D 3[/red]
E 5

Then when the second SQL runs it changes to this:
A 1
[red]B 4[/red]
C 2
D 3
E 5

I put it in the wrong place, it shoulda been on the < (like in this example) instead of on the > as I said above.

 

[steven290]

look closely at this

The second SQL:
"UPDATE "&tablename&" set "&sortfield&"="&newpos&"-1 where "&idfield&"="&idtoupdate&";"

this give you 3

 

[nicklieb]

k I got a rough and dirty version based on the steven's original version. It does what I want it to do.

I will post once i have tidied up and all the debugging code has been removed.

thanks guys your input has been most helpful..

 

[steven290]

so in the first example it would make b=3 hence "place before feature

 

[Sheco]

Ah, didnt see the -1

Wouldnt that give you:
A 1
B 3
C 2
D 4
E 5

But I was trying to move B to the 4 rank...

Oh sheesh.

Now I get it!

I misunderstood what you meant by "Place before a certain record feature"

It makes perfect sense now.