Professional Web Applications Themes

Storing sequences - MySQL

This may be something I need to sleep on, but I'd be grateful for some tips. I need to create a delivery list, which I can maintain in order, and insert and move individual entries ("before" and "after"). There are a number of individual "runs", which should be separate, thus... SELECT DELIVERYID, RUNORDER FROM RUNTABLE WHERE DRIVERID = x ORDER BY RUNORDER ASC. The question is, let's say I want to move deliveryid x to another place in the runorder? How can I do that and "bump up" any existing deliveries above it so that when I run the query ...

  1. #1

    Default Storing sequences

    This may be something I need to sleep on, but I'd be grateful for some tips.

    I need to create a delivery list, which I can maintain in order, and
    insert and move individual entries ("before" and "after"). There are a
    number of individual "runs", which should be separate, thus...

    SELECT DELIVERYID, RUNORDER FROM RUNTABLE WHERE DRIVERID = x ORDER BY
    RUNORDER ASC.

    The question is, let's say I want to move deliveryid x to another place
    in the runorder? How can I do that and "bump up" any existing deliveries
    above it so that when I run the query again it's in the right place?

    Any thoughts for the structure or queries would be gratefully received,


    turnitup Guest

  2. #2

    Default Re: Storing sequences

    turnitup wrote:
    > I need to create a delivery list, which I can maintain in order, and
    > insert and move individual entries ("before" and "after"). There are a
    > number of individual "runs", which should be separate, thus...
    >
    > SELECT DELIVERYID, RUNORDER FROM RUNTABLE WHERE DRIVERID = x ORDER BY
    > RUNORDER ASC.
    >
    > The question is, let's say I want to move deliveryid x to another place
    > in the runorder? How can I do that and "bump up" any existing deliveries
    > above it so that when I run the query again it's in the right place?
    This is possibly a naive solution, but I have a waiting room situation
    in one of my applications. When someone is admitted, their appointment
    gets given an "admission order" value which is one greater than the
    previously assigned value. If someone else is admitted, the first person
    no longer has the highest value (lowest number means first served). If
    the more recent arrival needs to be bumped up the queue, I have two
    options: I can either make their admission order value lower, or I can
    make everyone else's higher. Since the first option involves the
    possibility of conflict, I chose the second option. So instead of moving
    them up the queue (by giving them a lower number), I move everyone else
    down the queue (by giving them all higher numbers). I use a 64-bit
    number for this, so it's *extremely* unlikely that I'm going to run out
    of admission sequence numbers.

    In your case, to send a run order to the end of the queue, you would do
    an update like this (maxRunOrderPlus1 is a value guaranteed to be at
    least one larger than the current maximum run order value--find it the
    best way you know how):

    UPDATE RUNTABLE
    SET RUNORDER = maxRunOrderPlus1
    WHERE RUNORDER = theRunOrderValueToBePushedBack;

    To move someone up the queue? Let's say that this is my run table:

    DELIVERYID RUNORDER
    2 34
    3 35
    4 35
    5 36
    6 37

    To move delivery #5 up one place, I would have to execute the following
    queries:

    UPDATE RUNTABLE
    SET RUNORDER = maxRunOrderPlus1
    WHERE RUNORDER = 35;
    UPDATE RUNTABLE
    SET RUNORDER = maxRunOrderPlus1
    WHERE RUNORDER = 37;

    That leaves my run table looking like this:

    DELIVERYID RUNORDER
    2 34
    5 36
    3 38
    4 38
    6 39

    Make sure you do it in a transaction, or lock the table while you're
    doing it if you don't have transactions.

    Phil.
    Phil Cairns Guest

  3. #3

    Default Re: Storing sequences

    Maybe have a look at this:

    [url]http://developer./yui/examples/dragdrop/[/url]

    and perhaps this (which I think better explains implementation)

    [url]http://www.phpriot.com/d/articles/client-side/sortable-lists-with-php-and-ajax/index.html[/url]


    turnitup wrote:
    > This may be something I need to sleep on, but I'd be grateful for some tips.
    >
    > I need to create a delivery list, which I can maintain in order, and
    > insert and move individual entries ("before" and "after"). There are a
    > number of individual "runs", which should be separate, thus...
    >
    > SELECT DELIVERYID, RUNORDER FROM RUNTABLE WHERE DRIVERID = x ORDER BY
    > RUNORDER ASC.
    >
    > The question is, let's say I want to move deliveryid x to another place
    > in the runorder? How can I do that and "bump up" any existing deliveries
    > above it so that when I run the query again it's in the right place?
    >
    > Any thoughts for the structure or queries would be gratefully received,
    strawberry Guest

Similar Threads

  1. Image Replacement - Effects? Sequences?
    By corywilkerson in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: February 24th, 07:11 PM
  2. Shared Sequences?
    By C. Duncan Hudson in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 2nd, 10:30 PM
  3. New Sequences for Batch Processing
    By Matt_Torbin@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 9
    Last Post: July 29th, 01:56 PM
  4. SOAP4r sequences and authentication
    By Martin Stannard in forum Ruby
    Replies: 1
    Last Post: November 11th, 02:16 AM
  5. reordering column sequences
    By BK Kim in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 8th, 11:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139