#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Renumbering numeric column +1 from the field updated or added


    Hi,

    I have done a lot of searching to find something similar to what I am trying to accomplish but am unable to. Here is what I have and what I'm trying to do.

    I have a mysql database using php for the front end. There is a table called cases which are sorted numerically by a column called priority. This starts with 1 and goes up to however many cases there are at the given time.

    Currently the person who manages the data in the database has to manually change the priority for every case when she adds or changes the priority of a case.

    What I would like to figure out is how if she changes a case from priority 5 to 4, then every field after 4 would get updated +1.

    Example:
    priority
    1 john
    2 jane
    3 george
    4 alex
    5 scott

    change alex to 3 would renumber following

    1 john
    2 jane
    3 alex
    4 george
    5 scott


    Also, I am not sure if this can be done with a trigger or if it would have to be done in php code.

    Thanks in advance!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by andrelic
    ... or if it would have to be done in php code.
    yes, the latter

    and it's quite ugly, and the processing hit on your table is excessive -- you end up updating the entire table if you wanted to move something to priority 1 (which happens often, right?)

    i've seen this problem done with floating point numbers, though, which is really neat, because if you have to move an item, you simply update its number to the average number in between the two numbers that you want the moved item to end up between, and you can keep doing this thousands and millions of times, because they're floating point numbers

    of course, when you display them, you number them consecutively starting at 1, but they keep their floating point number under the covers

    this way, you only ever update the item that's moving
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0
    I think I understand what you're saying and that you understand my environment.

    The numeric field they can put any number doesn't matter. I have the php code specified to order the output numerically based on the number in the priority field.

    I wasn't sure if it was possible or how difficult it would be to automate the process of changing all data fields in that priority column following the field updated.

    I guess I may need to tell them it wont happen unless someone knows how I can make this happen.

IMN logo majestic logo threadwatch logo seochat tools logo