Getting a MySQL last insert_id from an ADOdb connection

The PHP ADOdb libraries are a database abstraction layer that tries to hide the database specific commands from the programmer.  It tries to allow the programmer to write code that will be portable between any backend database engine.  Since not all databases provide an insert id, ADOdb provides a wrapper for it in the form of it’s Insert_ID() function.

It implements it in a really ugly way though. Whenever you use it’s pseudo insert_id functions, it creates a _seq table with a single column and a single row. For example, if you are inserting something into a table named ‘users’, it will create a table named ‘users_seq’ with a single ‘id’ column. It generates one row in that column with an insert id that it calculates and increments on it’s own.

First off, that is really ugly. I hate having a whole bunch of extra tables in my database, and it makes it even worse that they only have a single value in them. I wish they would have implemented that differently, and made a single ‘_sequences’ table with two columns (table and id).   At least that would keep the tables to a minimum and centralize where all of the insert id’s are at.

The other bad part about it, is if you access the database with anything other than the ADOdb application, it is difficult to use this required structure. In most cases, things break and I get duplicate key constraints and it is just generally a pain.

So I’ve decided not to use it ever again. Its not likely that I’ll ever change the database anyway, so I might as well take advantage of the handy insert_id functionality already provided by MySQL.  Just do your queries as you would normally, including the ‘INSERTID’, and then you can retrieve the insert_id like in this example:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL,
) ;
// I assume you know how to create a ADOdb object
$db ->query("
    INSERT INTO USERS (id, name, email) VALUES ('INSERTID', 'Joe User', '')
$user_id = $db->_connectionID->insert_id;

2 thoughts on “Getting a MySQL last insert_id from an ADOdb connection”

  1. I’ve been using the Insert_ID() call in adoDB for a bit now, it is messy but I couldn’t find anyway around it at the time besides creating my own temporary table. And i didn’t want to just get the last record either as my Database is busy and could have easily let to the wrong record being returned if two queries clashed. I’m still dubious about all the methods that at some stage they could clash in theory .

  2. I’ve been thinking about writing a patch to adodb to include all of the _seq tables into a single _sequences table with two coluumns. I personally would like that a lot better just to avoid having all of the extra single-column, single-value tables.

Leave a Reply

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