mysql autoincrement field

jakem

Okay, I have some data that gets input into my database...
That works just fine, each row has an id number that is just an auto-increment field. The problem is this, when the user is entering the data, it is necessary for them to know what the id # for this entry is going to be before they click the submit button...since it is likely that several users may have the submission form open at the same time, it's not as simple as just finding out the max id number and adding 1 to it and displaying that on the page. (then several users could see the same id number even though the entry would have to have a unique id number). So does anyone have any ideas on how I could be sure the the id number displayed is the number that will actually be set by mysql?

The_Chancer

I use this myself and it does work:

Create a blank database entry based upon a userid and then pull the ID number out by:
SELECT id FROM dbasename WHERE x='' and user='username'

'x' is a field that will be automatically filled in when they submit the form and is not based upon the user filling in a box on the form.

That way noone else can use that row of the table. One way of getting the previous increment number in MySQL is by using a mysql_insert_id query.

Hope it helps

TTT

you can create system_table where is columns: table_name, current_value

current_value ++ (--> update system_table) = new id for other table

Bunkermaster

if you have the autoincrement option on why do you do all this?

just use the
INSERT INTO yourTable ( field1, field2, field3, field4, field5 )
VALUES( $field1, $field2, $field3, $field4, $field5 ) concidering that field0 would be the ID and you may notice I didn't use it. The mysql engine will take care of the details 🙂

The_Chancer

Ahh, I didn't make that clear in my statement of making a "blank database entry...."

Sxooter

If you HAVE to show them what number they're going to get, then you should do it something like this (I'm a postgresql guy, you'll have to translate to mysql)

On the page that builds the form you issue a sql query similar to:

select nextval('seqname');

And put that into the form. Then, when the form is submitted you do it thusly:

$val = value from the previous form's nextval...
insert into table (field1, field2, id) values ('$a','$b',$val);

And that should work. You'll get holes in your sequence each time someone gets a form and doesn't insert, but holes in sequence don't really mean anything bad. But holes in sequences are normal, so don't worry about them

ultraslacker

mysql_insert_id

Bunkermaster

good call

Blaxpear

If I understand what you are trying to do, then you must be assuming that only one transaction will occure at a time... ... you can't have more than one person filling/submitting their form at the same time (overlaping transactions)... because you'll risk showing the first person their future ID (AutoNumber) while another (second person) person submits before the first; the second person actually gets assigned the first person's autonumber. ...??

I hope I dont have this wrong. But I am also hard-pressed to understand why you would want to show the Key to a record that has yet to be created... ?? ...??

Blax

Bunkermaster

hmmm maybe reading the manual and having a look at a dictionary to learn the sens of the tricky word "previous" would be a good idea or maybe I am a sarastic dude... dunno

Sxooter

Assuming you're in a database that handles sequences in a transaction safe manner, then there is no danger in using nextval('seqname') since nextval on postgresql in particular triggers the sequence in increment.

As long as your database increments the sequence counter when you call for the next id, then you're safe to do this. I.e. this sequence is safe:

Client A: $val = select nextval('seq'); (A.$val=45)
Client B: $val = select nextval('seq'); (B.$val=46)
Client A: Abort transaction
Client B: insert into table (field1, id) values ('yada',$val);

will insert yada into the table, with id of 46, while a record with id of 45 will never be created.

Blaxpear

"Sarcastic" would be the word. But that's quite alright.

Just being "critical" is my nature; and I guess coming from an applications design standpoint, I'm having a little trouble visualizing a scenario where I would absolutely need to know the id of a record before I create it.

Sure we can create a blank record and say attach a sessionID and/or return INSERT_ID. But what happens to transactions that are not completed? Why should you have to deal with the empty records? I'm not challenging anyone's approach I'm just curious as to why we would go through more processes just to obtain the ID # to a blank record... I'm iterested in this guys scenario. If that helps you understand where I'm coming from Monsieur Biggs... ;-) Je suis juste un peu curieux.

Never Mind...

Sxooter

While I agree that printing the sequence number you're going to use on a user form is probably somewhat of a misguided idea, there are many times when it does work better to grab the sequence by hand before inserting.

One common reason for this is to make it simpler to insert data into related tables. Let's say that we have tables a and b, and table a has an auto-incrementing field we'll use as a reference in the b table. In that case, it may be faster and simpler to just grab the nextval, and insert it by hand. In most databases, sequences are the one thing that doesn't roll back, and they tend to have behaviour that at first appears odd in a transactional system, but they make sense in the greater idea of why sequences exist.

So, the procedure is something like this (php pseudo code)

insert into a (nom, plume) values ('$nom','$plume');
$seq = "select currval('seq');
insert into b (book, publisher, id) values ('$book','$pub',$seq);

On most databases, the currentval will remain the same even if another transaction has incremented the counter when you're within a transaction, since the current value for this transaction is what we're probably after.