[thelist] [MySQL] return values from query

[Beam]

On Thursday 13 February 2003 06:53 pm, Paul Bennett wrote:
> Is there a way to return a field value from an insert query? Instead of
> (1) inserting a new record and then (2) a select to get the match of the
> new record and get the row id from the query (auto increment, primary
> key), is there a way I can INSERT the new record and get mysql to tell
> me the row_id value without querying a second time?

assuming you are on PHP:
http://www.php.net/manual/en/function.mysql-insert-id.php

and there are equivalents for other langs as this is part of the mySQL C API.
But make sure your setup is vanilla - no INSERT DELAYED or other fancy
tricks. And it _must_ be called as part of the same db connection that did
the INSERT. For that reason I use the PEAR class DB.php which has a
$db->nextID() function which emulates sequences for mySQL. Plus if you ever
move to postgres or something you will have a lot less work to do. But there
are caveats to it as well which I won't get into.
S