auto_increment after inserting a record with specified id.

[hungtx]

HI everyone, I have this problem.

Example I have table like this:

ID is auto_increment field.

 

---------------

ID | Name

1    A

2    B

3    C

4    D

---------------

I now, I insert a record with specified id = 10 , for example, with name is "K"

So, My table now look like this:

 

---------------

ID | Name

1    A

2    B

3    C

4    D

10  H

---------------

And I know, value of auto_increment now is 10, if i insert a record without specified id value, value of auto_increment will be 11.

 

Problem is:

I want to insert record into my table, with next auto_increment id will be 5, and so on, next is 6,7,8,9 (don't need to be in order) like this:

 

---------------

ID | Name

1    A

2    B

3    C

4    D

10  H

5    X

6    F

7    P

8    K

9    L

---------------

 

At this time, when i insert a record, auto_increment value will be 11. And now, my table is:

 

---------------

ID | Name

1    A

2    B

3    C

4    D

10  H

5    X

6    F

7    P

8    K

9    L

11  O

---------------

 

Can I do this, I try to get the value before inserting the record with id = 10 into var x, when i insert this ( id = 10), then i try to reset auto_increment with value of x, but this isn't correct.

 

Can anyone me help ? With Php/Mysql code ?.

Thank you a lot. 

[trq]

You can't insert a value into an auto incrementing filed, why you would is beyond me.

 

An auto incrementing filed should be left alone, not relied on to order data, and never reset. Maybe you need to tell us the bigger picture of exactly why you think you need to do this.

[hungtx]

I know, auto_increment should be left alone.

Why do I think like this ?

Because, I have and forum, and I create a thread_id (auto_increment) field for thread posted by user.

I let user choose the lucky and beautiful number like 7777, 8888, 9999... just about ten value, for lucky user (most-posted-thread user). so their thread willl be post with this value.

Then I create a search field, so those user, and visitors can easily search those "special" thread via entering above value: 7777, 8888, 9999.... then read them.

and the reason I want to like the way I mentioned is I don't want to waste the value between the current id and specified id. value from 5-9, example.

 

[hungtx]

anyone help me please ?

[trq]

You'll want to create another field altogether to store this lucky id, it has absolutely no bearing on your data's integrity.