进行更新时订购表

Question

I have the following table where order by priority ASC

 ----------------------
|priority |activity   |
|---------|-----------| 
|   1     |act1       |
|   2     |act2       |
|   3     |act3       |
|   4     |act4       |
|   5     |act5       |
|---------|-----------|

JSON where I make an update.

Add Method but it does not work as I wish

  <?php
      //update.php
      include_once('../include/conexion.php');

        $query = "
               UPDATE ACT_schedule SET ".$_POST["name"]." = '".$_POST["value"]."'
               WHERE id_schedule = '".$_POST["pk"]."'";
            $result=mysqli_query($conn, $query);

            if ($result) {
            $query2 = "UPDATE ACT_Agenda SET prioridad = CASE 
                   WHEN prioridad >= " . $_POST['value'] . "
                   THEN prioridad + 1 ELSE prioridad  END
                   WHERE id_agenda <> '" . $_POST['pk'] . "'";
            mysqli_query($conn, $query2);
            echo "YES";
        }  ?>

What I want to do is order the priority, if I update the act5 that has priority 5 to priority 1, the priority changes and that means that the priority of the act1 must change to 2 and so on until the act4 change to priority 5.

It works well if I update the last priority. But if I update the act4 to priority 1 the ones below should not be updated but they do it by adding +1 (act5 priority 5 is 6).

Something like that I would like if I update act4 to priority 1

   ----------------------
    |priority |activity   |
    |---------|-----------|
    |   1     |act4       |
    |   2     |act1       |
    |   3     |act2       |
    |   4     |act3       |
    |   5     |act5       |
    |---------|-----------|

I hope I explained well. Greetings.

Answer 1

From your code it's not 100% clear which are the appropriate $_POST variables to use in the update query so here is a pure MySQL solution. First create the demo table:

CREATE TABLE agenda (`priority` int, `activity` varchar(4)); 
INSERT INTO agenda (`priority`, `activity`)
  VALUES (1, 'act1'), (2, 'act2'), (3, 'act3'), (4, 'act4'), (5, 'act5');
SELECT * FROM agenda ORDER BY priority;

Output:

priority    activity
1           act1
2           act2
3           act3
4           act4
5           act5

To update, use the following query. I have used variables @n for the new priority and @a for the activity to modify; in your PHP code you would remove the SET statements below and replace @n and @p in the update query with the appropriate $_POST values. In this example I am shifting act4 to priority 2:

SET @a = 'act4';
SET @n = 2;
UPDATE agenda SET priority = CASE WHEN priority BETWEEN @n AND (SELECT * FROM (SELECT priority FROM agenda WHERE activity=@a) a) AND activity != @a THEN priority + 1 
                                  WHEN activity = @a THEN @n
                                  ELSE priority
                             END;

Now we can look at the modified table:

SELECT * FROM agenda ORDER BY priority;

Output:

priority    activity
1           act1
2           act4
3           act2
4           act3
5           act5

SQLFiddle Demo