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.

    Basically, you can't do this automatically. It's just the way auto-increment works.

      Yes, I know that I can't do it with MySQl query.
      But can I do it with PHP code ?

        You could use this statement:

        SELECT MAX(id) id from mytable

        That would give you the highest current id, then you could just increment by one and use the new value--presuming that's what you want to do.

          hungtx;10920926 wrote:

          Yes, I know that I can't do it with MySQl query.
          But can I do it with PHP code ?

          Not in any practical fashion. Each time you wanted to add a new record you would have to query all the records from the table then find the first hole to insert the new one.

          You would be far better off to just rethink your strategy.

            Randy Jackson;10920929 wrote:

            You could use this statement:

            SELECT MAX(id) id from mytable

            That would give you the highest current id, then you could just increment by one and use the new value--presuming that's what you want to do.

            Can you tell me more detail about your way ? You can take some values from my table to describe your way. I want to try your way.

            Originally Posted by ahundiak
            Not in any practical fashion. Each time you wanted to add a new record you would have to query all the records from the table then find the first hole to insert the new one.

            You would be far better off to just rethink your strategy.

            Thank you. Maybe I have to rethink it.

              Can you tell me more detail about your way ? You can take some values from my table to describe your way. I want to try your way.

              The MAX function will pull the highest value from a column, presuming it can be quantified as such. So basically the statement:

              Select MAX(id) id from mytable

              Will pull the highest numeric value from that table, and assign it to the result 'id'.

              Now that you have that value, you can simply increment it by one to find the next number in sequence:

              $newid=mysql_result($result,0,'id);
$newid++;

              And that would give you the next number in sequence. So now you could use that variable to set your id field.

              I'm curious as to why you want to use an autonumber field if you're going to manually insert the id. This method would probably work much more effectively if you used an int field for the primary key, as I think you'd have fewer opportunities for conflicts.

                auto-increment works like that for a reason.
                The reason is to prevent that in ID is ever associated with two different recordsets. And that is good, because that protects data integrity. For example when you delete a record and if for any reason in a different table that referenced the ID, the associated data did not get deleted, and you reuse the ID the data would be matched to the new record - which of course would be wrong.

                So it is actually a bad idea to include a record with a specific ID like this, because you break the auto-increment functionality. Why do you feel this is necessary in the first place? There will be some other way to achieve what you want to...maybe we can help, so let's hear what it is you want to do

                Bjom

                  Thank all of you.
                  I will rethink my way. Maybe I will need your help. Thanks.

                    Write a Reply...