mam malutki problem... otóż mam sobie takie dwie tabelki:
Kod
mysql> select *from tree;
+--------------+------------+
| id | parent |
+--------------+------------+
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 5 |
| 6 | 2 |
| 7 | 2 |
+--------------+-----------+
7 rows in set (0.00 sec)
mysql> select *from category;
+----+--------------+
| id | nazwa |
+----+--------------+
| 1 | aaaaaaaaaa |
| 2 | bbbbbbbbbbb |
| 3 | ccccccccccc |
| 4 | ddddddddd |
| 5 | eeeeeeeee |
| 6 | fffffffffffffff |
| 7 | ggggggggg |
+----+--------------+
7 rows in set (0.00 sec)
+--------------+------------+
| id | parent |
+--------------+------------+
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 5 |
| 6 | 2 |
| 7 | 2 |
+--------------+-----------+
7 rows in set (0.00 sec)
mysql> select *from category;
+----+--------------+
| id | nazwa |
+----+--------------+
| 1 | aaaaaaaaaa |
| 2 | bbbbbbbbbbb |
| 3 | ccccccccccc |
| 4 | ddddddddd |
| 5 | eeeeeeeee |
| 6 | fffffffffffffff |
| 7 | ggggggggg |
+----+--------------+
7 rows in set (0.00 sec)
i nie wiem jak stworzyć zapytanie którego wynikiem będzie zamiana miejscami pozycji o danym id i o id mniejszym o jeden np.
chciałbym zamienić w tabelce tree i categorry pozycje która ma id=3 z pozycją o jeden mniejszą razem ze zmianą nazw id czyli wynikiem będzie:
Kod
mysql> select *from tree;
+--------------+------------+
| id | parent |
+--------------+------------+
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 5 |
| 6 | 2 |
| 7 | 2 |
+--------------+-----------+
7 rows in set (0.00 sec)
mysql> select *from category;
+----+--------------+
| id | nazwa |
+----+--------------+
| 1 | aaaaaaaaaa |
| 2 | ccccccccccc |
| 3 | bbbbbbbbbbb |
| 4 | ddddddddd |
| 5 | eeeeeeeee |
| 6 | fffffffffffffff |
| 7 | ggggggggg |
+----+--------------+
7 rows in set (0.00 sec)
+--------------+------------+
| id | parent |
+--------------+------------+
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 5 |
| 6 | 2 |
| 7 | 2 |
+--------------+-----------+
7 rows in set (0.00 sec)
mysql> select *from category;
+----+--------------+
| id | nazwa |
+----+--------------+
| 1 | aaaaaaaaaa |
| 2 | ccccccccccc |
| 3 | bbbbbbbbbbb |
| 4 | ddddddddd |
| 5 | eeeeeeeee |
| 6 | fffffffffffffff |
| 7 | ggggggggg |
+----+--------------+
7 rows in set (0.00 sec)
próbowałem coś takiego:
$sql = "UPDATE category SET id=".($id-1)." WHERE id=".$id." AND SET id=".($id+1)." WHERE id=".($id-1);
ale niestety to nie działa ...
macie jakieś pomysły
