Help With Stored Procedure

Hello everyone. I am new to mySQL, so I am hoping someone might be
able to help me. We have a table in our database, which has a table
Ticket Priorities, which only contains 3 columns...User_ID, Ticket_ID
and Priority. This table basically houses all support personnel and
the support tickets assigned to each, and the priority of each
ticket. Example:

USER_ID Ticket_ID Priority
12345 006234 1
12345 006266 5
12345 006523 2
12345 006258 3
..
..
..
The application has a Priority screen, displaying the tickets for each
user in a list, ordered by priority. Our manager can log into the
tool, and reorganize the priority's by moving the ticket number up or
down using an up/down arrow. Every time the priority is moved, I need
a stored procedure to change the Priority number in the database. For
example, if a ticket with priority 5 is moved up 2 places in the list,
I need the procedure to change the 5 to a 3, and shift the rest of the
numbers down. Not sure if I explained this properly or not. Any
ideas? Thanks everyone

== Quote from Smitty (marksmithy69@hotmail.com)'s article
> Hello everyone. I am new to mySQL, so I am hoping someone might be
> able to help me. We have a table in our database, which has a table
> Ticket Priorities, which only contains 3 columns...User_ID, Ticket_ID
> and Priority. This table basically houses all support personnel and
> the support tickets assigned to each, and the priority of each
> ticket. Example:
> USER_ID Ticket_ID Priority
> 12345 006234 1
> 12345 006266 5
> 12345 006523 2
> 12345 006258 3
> .
> .
> .
> The application has a Priority screen, displaying the tickets for each
> user in a list, ordered by priority. Our manager can log into the
> tool, and reorganize the priority's by moving the ticket number up or
> down using an up/down arrow. Every time the priority is moved, I need
> a stored procedure to change the Priority number in the database. For
> example, if a ticket with priority 5 is moved up 2 places in the list,
> I need the procedure to change the 5 to a 3, and shift the rest of the
> numbers down. Not sure if I explained this properly or not. Any
> ideas? Thanks everyone

you explained it ok.

you need an sp that takes in as input parameters 3 values:
user_id
ticket_id
changed_priority_orig
changed_priority_new

you can lookup the basics of stored procedures on mysql's website; however, this
is what it should do
1-it takes the values mentioned above
2-it updates the record that has changed to changed_priority_new based on
changed_priority_orig
3-if the new priority is already existing in the dataset for that user_id, it then
needs to be updated to changed_priority_orig

i believe this should do it.
--
POST BY: lark with PHP News Reader