Welcome to Particletree!

When dealing with forms, you soon realize that whenever you perform an sql insert, it is usually followed by some method of unique id retrieval. Once you have a handle on the primary key, you can procede to update, delete, or modify in any way you see fit. The question is: How do you find the primary key of the last record inserted? Here are a few of your options:

• @@Identity works well for those of you using Sql Server. An interesting point to note is that the key returned is that of the open database connection. If you were to do an insert, close the connection, reopen the connection, then check the identity you would receive a null. Additional info can be found at the bottom of this link, and here is a great write up as well.

• mysql_insert_id() is a PHP Function that works with MySQL. It also seems that this function is session based, so you do not need to worry about overlapping ID’s in a high traffic site.

• last_insert_id is for those of us using MySQL, but remain true to ASP.

• The last option is a seed table. Create a table in your database with 3 columns: seedID, tableName, seedValue. This table will manage primary keys for any other table in your database. Before every insert, query this table to find the next available primary key. During each insert, make sure you increment this table so it is accurate for the next insert. I could not find a good tutorial on the subject, so I guess I’ll do a write up on it soon.