[thelist] SQL user-defined order

[Matt Slocum]

I got it working. This is basically what I ended up doing.

// if move up
UPDATE Category SET position = position - 1 WHERE position <=
@MOVED_TO AND position > @MOVED_FROM;
UPDATE Category SET position = @MOVED_TO WHERE id = @MOVED_ITEM_ID;

// if move down
UPDATE Category SET position = position + 1 WHERE position >=
@MOVED_TO AND position < @MOVED_FROM;
UPDATE Category SET position = @MOVED_TO WHERE id = @MOVED_ITEM_ID;

Thanks all. I'm a newbie on this list, but you guys are great!

Matt

On 10/10/07, Ken Snyder <kendsnyder at gmail.com> wrote:
> On 10/10/07, Matt Slocum <matt.slocum at gmail.com> wrote:
> > I want the user to be able to decide the order of a list of
> > categories. The way I first tried to do it is making an 'order' field
> > that keeps a numerical order. The problem is that if the last item is
> > moved to the beginning, then every 'order' value has to be updated.
> > This is possible, but very inefficient.
> >
> > Does anyone have any ideas?
>
> A couple thoughts.
>
> You can typically change sequence in the database based on the moved
> item id and its new position using just two queries:
> UPDATE category SET sequence = sequence + 1 WHERE sequence >= @MOVED_TO;
> UPDATE category SET sequence = @MOVED_TO WHERE id = @MOVED_ITEM_ID;
>
> When moving the last item to the beginning, @MOVED_TO would equal 1.
>
> Most likely, performing just these two queries often shouldn't
> overburden the database. The alternative (using a submit button or
> setting a timeout for a few seconds then doing one query for each item
> setting resting sequence based on the id) is fine, just not as user
> friendly.
>
> - Ken Snyder
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


-- 
Matt Slocum
864.270.9895
www.MiracleMultimedia.com