[RESOLVED] Inserting data between rows in MYSQL

[sc_king]

How can insert data between 2 rows where id is the primary key and auto_incremented.
I don't want to insert it at the bottom, unless I have to.

[chazzy]

I'm not sure what you mean. You're saying you deleted, for example id 6, and now want to add it back?

[sc_king]

For example:

id name
1 a
2 b
3 c
4 d

Is there a way to insert a new row between id 2 and 3 and shift id 3 and 4 to 4 and 5?

id name
1 a
2 b
3 new
4 c
5 d

[Nedals]

The simple answer is No. Auto-increment will not allow you to do that.

You could, however, create some addition code to exchange the values.
ie:
Get value3 at id=3; update with 'new' at id=3
Get value4 at id=4; update with value3 at id=4
etc,
Finally insert value5. (in your example)

Looks complicated, but a simple loop of code will do the job. Even without auto-increment you would still have to do something like this.

[sc_king]

ok thanks!,

just making sure there wasn't a simpler way.