MySQL Case Statement (if else if equivalent)

Question

I'm attempting to re-order a list based on their positions upon updating the position of one item via the submission of a drop-down box (which has a name assigned to the PHP variable $position). Here's an example, with positions represented by their order in the list:

1. Item1
2. Item2
3. Item3
4. Item4
5. Item5

I'm using the following MySQL query:

UPDATE subjects SET 
position = position + 1 
WHERE position <= position AND position >= {$position};

If I was to move Item5 to position 2 this query would push everything from position 2 to 4 downwards by one position, resulting in:

1. Item1
2. Item5 <<< moved up
3. Item2
4. Item3
5. Item4

However, If I wish to do the opposite and move an item downwards, the position would need to become one less (position = position - 1), and the <= and >= signs would swap. Therefore I'm wondering how I would do this in a case argument (an equivalent to if{} else if{})

Answer 1

If you need the if else you can use the MySQL case statement (link). It basically works as follows:

CASE case_value
  WHEN when_value THEN statement_list
  [WHEN when_value THEN statement_list] ...
  [ELSE statement_list]
END CASE

And you can nest them as required.