mysql_insert_id

[jonathen]

Hi all,

Still cannot get this to work. I have a form that once filled in, needs to dump the fields to mytable in the db, and redirect to an output page that displays the dumped data in pretty html.

The only thing is, I need it to also display the id (unique, auto_increment) along with all the fields within the row.

I cannot for the life of me figure out mysql_insert_id, which I understand is the only way to get the last id from the table.

Anyone got any ideas? I'm pulling my hair out here!

TIA

Jonathen

[immot]

You can select max(id) before insert-statement, and then insert max(id)+1. Now you know that the last id inserted is max(id)+1. In this way you must make sure that both select and insert are in the same transaction and that the transaction isolation level is serializable.

• immot

[Flurp]

you could also make a query and ask for id where eg. name= name just entered, adres = adres just enterd and so on... if you get more than one lines back just use the last one (unless you order them by id (falling order).. unless you have hundres and hundreds of ppl visiting that page every moment chanses are pretty big you return the wanted id...

understand? hope it helps!

[jonathen]

Yes, I think I see what you are saying. Going to try it out in a minute, after I finish my current script. Thanks for your help 🙂

[fitchn]

Originally posted by immot
You can select max(id) before insert-statement, and then insert max(id)+1. Now you know that the last id inserted is max(id)+1. In this way you must make sure that both select and insert are in the same transaction and that the transaction isolation level is serializable.

Do NOT use this approach! This can cause some major problems if you happen to have two or more users submitting the form at roughly the same time (which is very possible, when you cnsider the fact that litteraly millions of people have access to your page at any given time). At best, the last user would overwrite the data from the previous user(s), since they would both be using the same key. Your data and/or table could also become corrupted or worse! This method, while seemingly simple, presents some major data integrity, security, and stability issues! Again I say, do NOT use this approach!

• Nick

PS: Nothing personal, immot, just making people aware of the potential consequences of your proposed method.

[fitchn]

When using mysql_insert_id(), don't forget to include the ()'s at the end of the name (it is a function, not a variable). Also note that it can only be used when data has been inserted into the table using an autoincrementing field.

Hope that helps!

• Nick

[immot]

fitchn: ...and that's why I mentioned transactions. I see no harm in this if the transactions are handled properly.