Ordering Items by Priority

[Anon]

Hi everyone. I am currently working on a PHP/MySQL project and would appreciate suggestions on this step.

I have a table filled with various tasks for employees. One task could be programming while another could be graphic design. Each task has a different priority number with 10 being the highest priority and 1 being the least.

The administrator of the database is allowed to add tasks to the list and assign a priority or simply take an existing task and change the value.

Here's the catch. Let's say that user X has 10 priorities and the admin decides to add another task to the list and rank it number 5.

I need to find a way for it to bump everything from 5 to 1 down 1 spot (thust knocking 1 out) while it adds the new 5 to the list.

What is the best way to go about this?

Thanks in advance,
Charles

[Anon]

you could use the UPDATE syntax:

update tasks set priority=priority-1 where priority < $value

then insert the new task:

insert into tasks (rowID, name, priority) VALUES (NULL, 'new task', $value)

then dump the zero task:

delete from tasks where priority=0

craig