Auto Increment Ordering

Question

I have a table with a auto-increment id which orders the rows. I made a form where you can change a row's order.

id item
1 - row a
2 - row b
3 - row c
4 - row d

But the primary key(auto-increment) does not allow doubles like the following:

id item
1 - row a
2 - row b
2 - row c
4 - row d

Is there a way to change the order using a mysql or php sript so i can rearrange the order:

id item
1 - row a
2 - row b
3 - row d
4 - row c

in the example above row d and c have changed, eg. row c has moved 1 up and d has moved 1 down.

Any help would be appreciated!

UPDATE

The rows are dynamic. if, say, item c is deleted the count would be 1, 2, 4. How would you make that they close the gap if some are deleted

@SebastianOpperman, Use another field for the ordinations. You can use an INT type field that allows null values. — Lobo, Jan 23 '12 at 09:25
I think may relate https://stackoverflow.com/questions/73670896/how-to-add-auto-increment-id-in-mysql-table-based-on-sorted-sequence-of-another/73671201#73671201 — Johnbosco Adam, Sep 10 '22 at 10:56

score 4 · Answer 1 · answered Jan 23 '12 at 09:24

4

Add order field and use that for ordering

id item order

1 - item a - 1

2 - item b - 2

3 - item c - 4

4 - item d - 3

SELECT * FROM table ORDER BY order

answered Jan 23 '12 at 09:24

Vertigo

2,714
1
22
24

Thanks for the answer, the rows are dynamic. if, say, item c is deleted the count would be 1, 2, 4. How would you make that they close the gap if some are deleted – SebastianOpperman Jan 23 '12 at 09:27
3

@SebastianOpperman not need to close gaps. The order remains the same. – Your Common Sense Jan 23 '12 at 09:28
If you really want to "close the gaps" I'd suggest not doing it in the database but instead doing it afterwards in PHP, e.g. by using ksort() – dtech Jan 23 '12 at 09:32
UPDATE table SET `order` = `order`-1 WHERE `order` > 3 – Vytautas Jan 23 '12 at 09:33
@Col. Shrapnel if you change item c to 2,than both items c and b will be 2. which will be first of the two? – SebastianOpperman Jan 23 '12 at 09:33
@SebastianOpperman what's the point in assigning the same order for 2 fields? – Your Common Sense Jan 23 '12 at 09:39

score 0 · Answer 2 · answered Jan 23 '12 at 10:15

The ID column of your table serves no function except to uniquely identify records in it. If you want to access record number 3 then record number 3 should always be the same record. If you delete record number 3 then someone tries to retrieve it, it should return the fact that record number 3 doesn't exist anymore, not some other record that's been assigned the value 3 for its ID.

You need to remember that autoincrement primary keys are only there for identification purposes and serve no other semantic purpose in your data. Except for retrieving specific records and making sure that all records are unique in some way, they can be safely ignored.

If the data you want needs to be in a specific order, then you can use ORDER BY to specify the column(s) to sort by. If none of your columns provide sufficiant data to sort as you want, then consider adding a 'sort' column which holds a value for the order the item should be in the result set, then ORDER BY that column.

score -2 · Answer 3 · answered Jan 23 '12 at 10:24

-2

If you want to close the gap, do not make Id field auto increment, when inserting a new row get the maximum id entry from table and add 1 to it, in that case there will be no gaps when you delete some rows in between.

answered Jan 23 '12 at 10:24

Naveen Kumar

4,543
1
18
36

1

This violates the whole point of an ID, because the same ID might refer to different records at different times. – GordonM Jan 23 '12 at 10:45

Auto Increment Ordering

3 Answers3