Better last key inserted function for mySQL in ColdFusion8
I guess I should read the documentation for ColdFusion 8 more often. Turns out, there is a new feature when doing database inserts. In the CFQUERY tag, add the parameter result=”somename”. You can now reference the data inserted into the query, as well as, a really cool new feature, the last inserted key. Each database type has a different name for this. MySQL calls it GENERATED_KEY. You no longer need to do a second query to get back the last_insert_id().
<cfquery datasource=”#application.dsn#” result=”insertPhoto”>
INSERT INTO kidPhotos
(subject, body, dateOf, siteType)
VALUES
(<cfqueryparam value=”#subject#” cfsqltype=”cf_sql_varchar”>,
<cfqueryparam value=”#body#” cfsqltype=”cf_sql_varchar”>,
‘#dateformat(date, “yyyy-mm-dd”)#’,
‘ap’)
</cfquery>