Can I reorder a list in Flex and have it reflect in the db?

Hi all,

Bit of a tricky one here for me, let''s say i have a database with 5 entries, with a column called 'priority' which is set to numeric with values 1 - 5, and when i query the database i order them asc, ie:


<cfquery name="q" datasource="#dsn#">
SELECT priority
FROM myDataBase
ORDER BY priority asc
</cfquery>


I then show these items in a List control in Flex, now what i'm trying to do is develop something where i can reorder these in the database, ie changing the priority number.

I've tried using a drag and drop into another list control to reorder them, and that's fine visually, but how would i send that ordered list into the database?

Another thought i had was perhaps to have move up/down buttons, so that when an item is selected in the List control, it would grab the ID and priority from the database, then preform a calculation (don't know what) to +/- 1 from the priority, but it's a bit much for me to work out on my own, and also how would the others in the database get re-arranged?

Does anyone here have any experiance with this sort of thing or could someone point me in the right direction for a good tutorial on something simular? Any help would be gratefully appreciated! :)

Many thanks in advance all! :D

Here is an example of how you might do it:
<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml">
<mx:Script>
<![CDATA[
import mx.controls.Alert;

private function updateDB():void
{
var temp:String = '';
for(var i:String in itemList.dataProvider)
temp += itemList.dataProvider[i].data + '\n';

Alert.show(temp);
}
]]>
</mx:Script>

<mx:List id="itemList"
dragEnabled="true"
dragMoveEnabled="true"
dropEnabled="true">
<mx:ArrayCollection>
<mx:Object label="Item 1" data="1" />
<mx:Object label="Item 2" data="2" />
<mx:Object label="Item 3" data="3" />
<mx:Object label="Item 4" data="4" />
<mx:Object label="Item 5" data="5" />
<mx:Object label="Item 6" data="6" />
</mx:ArrayCollection>
</mx:List>
<mx:Spacer height="30" />
<mx:Button label="Update"
click="updateDB()" />
</mx:Application>
I used an ArrayCollection for the data provider, but you could easily adapt this to work with your database/RemoteObject source.

That's awesome dude! Love how it works and keeps the order, the thing is i need to send that order back to the db and have it change the 'position' column in the relevant row, so if i moved recordID number 1 with a position of 1, to position 2 in the list, it needs to know that ID 1's position has changed to 2 and to update the database accordingly, do you think this is possible?

Great work by the way!!! :D

I'm still not having any joy with this, i can't seem to be able to break the string down into seperate numbers that i can pass back to the database to reflect the change...... Can anyone suggest a way this might be done? It won't let me use a Number value :(

(Or i'm just doing it completely wrong! :p)

Thanks in advance anyone! :D

Ok, i think i might be getting somewhere here, i think if i could get the rowIndex of this list, then if i drag and drop an item at the top it's row index should remain at 1 right? If so then all i have to do is send the rowIndex back along with the recordID??? I think..... :p

Ah, I should have clarified that a bit more. In my example the ArrayCollection was acting as the database, so the "data" field I used would be the "recordID" for your case. To update your database you could send back a delimited, ordered list that you get by looping through the dataProvider, and use the recordID to update the row. An example might be: <cfset numlist="4,2,1,3,5"> <!--- this is your list of the order from flex --->
<cfset order=1>
<cfloop index="num" list="#numlist#">
<cfquery datasource="#myDSN#">
UPDATE mytable SET order= #order# WHERE id = #num#
</cfquery>
<cfset order++>
</cfloop>

This code has not been tested, it's just an example off the top of my head(CF is not my forte).

Of course!! :D A delimited list! Dude you are a star :p I'll have a play with this but i'm pretty sure you're right. You rock! lol

Oh, love the avatar by the way, one of my fav films... 'Hello, my name is Inigo Montoya, you killed my father, prepare to die' :p Awesome!! :D

Once again thaks for this, you've been such a great help :D

Peace :)

Believe or not, but this thread helped me even now, after so much time
So thanks a million!!!