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!

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

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.