PDO and last insert id

[Robin]

I wanted to make my code DB-engine independent so I thought I'd use PDO.
But then discover that PDO::lastInsertId doesn't work consistently
across all DB-engines. Further reading, reveals comments that what I had
been doing was 'evil' but give no ideal solution.

I want to add a record into a table that has an auto increment /
identity id field, then be able to get the id field's value so that it
can be displayed/used in another table/used elsewhere in the code.

I'm thinking of doing this:

1) insert new record with unique identifier
2) select using the unique identifier to get the new record

Two DB calls for each insert so not efficient.

Anyone have any better ideas/comments? Am I doing something else
fundamentally wrong?

Thanks
Robin

["Álvaro G. Vicario"]

Robin escribi�:

I believe you have two options:

1. Use auto-increment and write PHP code to make it portable enough
2. Use something else

For option #1... If the abstraction layer does not abstract a specific
feature, you'll probably have to build your own abstraction based on
vendor-specific code for each DBMS you want to support. That way you
need to use your abstraction to perform inserts but it doesn't affect
the whole present and future design of your application logic. Use
PDO::lastInsertId() when supported and write a workaround when not.

You should also note that you still have to write DBMS-specific code for
the table definitions. Apart from data types being different, you can't
image how much the auto-increment internals vary. Oracle, for instances,
requires writing a sequence and a trigger for each table.

As about option #2, you can use long unique identifiers generated by
PHP. This way there's no need to query the database after an insert: you
already know the ID.

--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

[Jerry Stuckle]

Getting the last insert id is actually quite quick and efficient. And
if your script takes that much time/resources, you will have better luck
optimizing other areas of your code.

Don't fall into the trap of premature optimization!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================