Get the ID of a newly created record from a ColdFusion insert query using CFquery result attribute
When inserting a new row to a mySQL database table using <cfquery>, it is often necessary to find the ID of the new record so you can refer to it in subsequent queries or other code further down the page.
There are a few ways to do this, but I like this one best:
<cfquery name="insertRecord" datasource="#request.dsn#" result="insertResult">
INSERT INTO my_table
(
column_1,
column_2,
etc
)
VALUES
(
'#value1#',
'#value2#',
'etc'
)
</cfquery>
<cfset newID = insertResult.generated_Key>
Notice the "result" attribute of the cfquery tag, which will accept any variable name you want to use. Then, immediately following the query code, we simple get the 'generated_Key' node of the result value, and voila - we have the ID of the new record, ready for whatever is needed next.
(Note: This example is for mySQL - not sure how this plays out in MS SQL or other DB engines... your mileage may vary.)
thanks
result_name.GENERATED_KEY
MySQL only. The ID of an inserted row. MySQL 3 does not support this feature.
In CF9 its both those names and a generic one. The exact name of which I have forgotten.
<cfset newID = insertResult.identitycol>