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', 'firstname.lastname@example.org') "); $user_id = $db->_connectionID->insert_id;