Ryan, A nice way to handle the resorting of your table in this case is to do a single update query that increments all the items after your currently selected item. eg.
1. Push all items after your current one down. UPDATE myTable SET itemOrder = itemOrder + 10 WHERE itemOrder >= #selectedItemsOrder# 2. Update the currently selected item with the correct order UPDATE myTable SET itemOrder = #selectedItemsOrder# WHERE itemID = #selectedItemID# Works a treat resorting bigger tables saving the DB from doing all those updates. Cheers, Mike Gardiner. -----Original Message----- From: Ryan Sabir [mailto:[EMAIL PROTECTED] Sent: Monday, 31 January 2005 2:29 PM To: CFAussie Mailing List Subject: [cfaussie] Re: SQL ordering question JS> What's your reason for sorting them in the DB? What happens when you Funny how when you think about something you start to wonder whether you needed it in the first place... Ok this is why... I've developed a widget that allows a user to sort items by clicking an up or a down arrow next to the item. To facilitate this here is what I do: 1. Have the ordering field separated by 10 2. When a user clicks the up arrow, subtract 15 from the ordering field, then re-sort the whole database, separating the items by 10. or when the user clicks down, add 15 then re-sort again. It also allows easily to insert a new item at any point, just insert it and run the re-sort code again. Thinking about it now, I should probably do something that's less work on the database, like just swap the 2 items... but this way was the easiest to quickly come up with in my head. bye! ----------------------- Ryan Sabir Newgency Pty Ltd 2a Broughton St Paddington 2021 Sydney, Australia Ph (02) 9331 2133 Fax (02) 9331 5199 Mobile: 0411 512 454 http://www.newgency.com/index.cfm?referer=rysig --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/ --- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
