Entry

How do I find the last insert id for an autoincrement field?

Feb 19th, 2008 22:28
dman, Seamus Venasse, Knud van Eeden, http://sturly.com

When using a table which has an autoincrement field, it may be useful 
to determine the ID value of the record for other operations.

For example, here is a "site" table for a list of URLs:

CREATE TABLE site (
  id int( 10 ) DEFAULT '0' NOT NULL AUTO_INCREMENT,
  name varchar( 25 ),
  url varchar( 50 ),
  PRIMARY KEY( id )
);

Here is the Z SQL Method to insert values and return the ID value for 
the record:

ID:  SQLsite_insert
Arguments: name url
Query Template:
insert into site( name, url ) values( 0,
<dtml-sqlvar name type=string>,
<dtml-sqlvar url type=string>)

<dtml-var sql_delimiter>

select LAST_INSERT_ID() as lastid
----------------------------------------------

Here is the DTML code to insert a record and retrieve the ID number:

<dtml-in "SQLsite_insert( name='Polaris Computing', 
url='http://www.polaris.ca/' )">
  <dtml-call "REQUEST.set( 'lastid', lastid )">
</dtml-in>
<dtml-var lastid>

Now that the ID is saved in a global variable, it can be used later on 
for adding joins to another table, sending an administrative email, etc.