Posted on

get the id of the new record you just inserted

i’m a web developer by trade, and one of the things i do on a pretty regular basis is try to find an easier, more efficient, or more effective way of doing the stuff i do all the time.

i do a lot of project work and one thing i do a lot of is created admin control panels. i prefer to use javascript to post the form data and have the server return JSON formatted data back so that more javascript can update the form.

one problem i was having was retrieving the id of the record i just created. in order to save additional changes to the form data, we’re probably going to need that record id, right? i knew that mysql must have a way of doing that, so i did some digging.

below is a very simple coding example where i first create a new record using some post data, and then return the record id in JSON. i primarily code in javascript on ASP, so that’s what this example is in. however, the idea can easily be translated.

var stuff = Request.Form('stuff').Item;                      

var dbc = Server.CreateObject("ADODB.Connection");
var cmd1 = Server.CreateObject('ADODB.Command');
var cmd2 = Server.CreateObject('ADODB.Command'); 
                                                   
dbc.ConnectionString = mysqlstr;                             

dbc.Open;                                                              


s =  "insert into mytable(stuff) values(?) ";

cmd1.Parameters.Append(cmd1.CreateParameter('i00','201','1','256',stuff));

cmd1.ActiveConnection = dbc;
cmd1.Prepared = true;
cmd1.CommandText = s;


s = "select last_insert_id() as NewID from tref_requests;"
cmd2.ActiveConnection = dbc;
cmd2.Prepared = true;
cmd2.CommandText = s;


dbc.BeginTrans;

cmd1.Execute;
rec = cmd2.Execute;	

if (!rec.EOF) {
	newRecordID = rec.Fields('NewID').Value;
}

dbc.CommitTrans;

dbc.Close();
	

what we just did was open a transaction, insert the data, immediately select last_insert_id(), then end the transaction.

so what is last_insert_id()? its a function that mysql provides for you to use to do exactly what we just did. it simply returns the record id of the very last insert you made. its important to note that it should be used on tables with automatically incrementing primary keys, because that’s the value its going to return. its also important to note that it probably won’t work outside of a transaction, which is why we wrapped up the insert and the select into a single transaction.

i hope somebody finds this helpful. it took me a little while to find all the pieces to put this together.

Leave a Reply

Your email address will not be published. Required fields are marked *