I have a table in which the first column represents the order in which the
data in the rows will appear in an HTML table on a Web page. The column is
called "ordr" (to avoid conflict with the reserved keword "order"). This
column is a primary key, but the values are not auto-incremented, they are
assigned by hand at first and supposed to be revised by a PHP script. This
is where the problem comes in. Suppose I want to change the order of a
record with respect to the HTML table by changing the value of the "ordr"
column for that row. Suppose I have five rows:
1
2
3
4
5
And I want to move the row currently at ordr 4 to ordr 1.
Step 1. First, I can set the row with ordr 4 to ordr 0 to set it aside:
1
2
3
0
5
Step 2. Then I would raise each item's ordr value by 1 if it is >= 1 and <
4:
2
3
4
0
5
Step 3. Finally, I would change 0 to the desired target ordr value, 1:
2
3
4
1
5
But how can I accomplish step 2? It would seem at first as if the following
should work:
update table my_table set ordr = ordr + 1 where ordr >= 1 and ordr < 4;
But this results in an error. When MySQL tries to update the first row from
1 to 2, it causes a (temporary) duplication of the value 2 in this unique
(primary key) column.
How else am I supposed to do this?
Thanks!
Amittai Aviram
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
