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.
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
I got your point... Basically you need to edit sort order numbers in your column Priority...
is your priority colum is primary ? or separate id column is there in your mysql table.. ? if separate primary id column doesn't exist.. create it and keep this priority column separate, so you can change/update sort numbers in this column....
in your ajax update script..i.e.... updatePriorityJSON.php.... you will need code somewhat like.. this is not tested.. but given as sample...
for($i=0; $i<count($_POST["priority"]); $i++) {
$query = "
UPDATE table
SET priority = '".$i."'
WHERE id = '".$_POST["id"][$i]."'";
}
more tutorial point of view, please view https://www.webslesson.info/2017/06/sorting-table-row-using-jquery-drag-drop-with-ajax-php.html?m=1
Please take care for prevention of any SQL injection through this code
After updating act5 priority to 1: (*)
UPDATE ACT_Agenda SET priority = 1 WHERE activity = 'act5';
Check to see the other records that need to be updated:
SELECT * FROM ACT_Agenda WHERE priority >= 1 AND activity <> 'act5';
Then update the priority: (*)
UPDATE ACT_Agenda SET priority = priority+1 WHERE priority >= 1 AND activity <> 'act5';
(*) last step is to implement this sql to your code
This can be achieved all inside the database. Please see the following example proof:
First the boring part where I created your table and inserted your sample data:
mysql> CREATE TABLE priority_demo (priority SMALLINT UNSIGNED, activity VARCHAR(4));
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO priority_demo VALUES (1, 'act1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO priority_demo VALUES (2, 'act2'), (3, 'act3'),(4, 'act4'), (5,'act5');
Query OK, 4 rows affected (0.02 sec)
mysql> SELECT * FROM priority_demo;
+----------+----------+
| priority | activity |
+----------+----------+
| 1 | act1 |
| 2 | act2 |
| 3 | act3 |
| 4 | act4 |
| 5 | act5 |
+----------+----------+
5 rows in set (0.00 sec)
Now, as an example, I'm changing the priority of act4 (currently prio 4) to new value 2. As my example is generic then I'm gonna use internal variables for both current and new priority for the specific activity, whereas ideally instead of @activity_key and @new you'd use your php variables when constructing the query:
mysql> SET @activity_key := 'act4', @new = 2; -- variables from php
Query OK, 0 rows affected (0.00 sec)
For demo purposes I'm outputting them too:
mysql> SELECT @activity_key, @new; -- variables used below
+---------------+------+
| @activity_key | @new |
+---------------+------+
| act4 | 2 |
+---------------+------+
Ok, we're ready to act now. First the most interesting query - the UPDATE with priority changes.
As pulling the current priority out of the database is un-necessary overhead and useless round trip, I've implemented 3rd MySQL variable here, called @cur which contains the current value, so the update query below would know if and where and what to change.
We run the 2 following queries (SET @cur ... and UPDATE ...) together as one batcho. This ensures that it's not gonna change anything if already changed:
mysql> -- both in one "batch"
-> SET @cur := (SELECT priority FROM priority_demo WHERE activity=@activity_key LIMIT 1);
-> UPDATE priority_demo SET priority=CASE
-> WHEN priority >= @new AND priority < @cur THEN priority+IF(@cur<>@new, 1,0)
-> WHEN activity = @activity_key THEN @new ELSE priority END;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 5 Changed: 0 Warnings: 0
Let's see the result:
mysql> SELECT * FROM priority_demo; -- result as is
+----------+----------+
| priority | activity |
+----------+----------+
| 1 | act1 |
| 3 | act2 |
| 4 | act3 |
| 2 | act4 |
| 5 | act5 |
+----------+----------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM priority_demo ORDER BY priority; -- priority ordered view
+----------+----------+
| priority | activity |
+----------+----------+
| 1 | act1 |
| 2 | act4 |
| 3 | act2 |
| 4 | act3 |
| 5 | act5 |
+----------+----------+
5 rows in set (0.00 sec)
Even if you reran the query again, it wouldn't change anything. That's because of the IF(...) part of the UPDATE above.
EDIT: I just discovered that my answer is somewhat similar to @nicks, but that's ok, I guess, as my proposed implementation can be safely executed in a "shoot firt, ask questions later" manner ;)