European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Databases :: General :: getting new record id on autoincrementing field
By: Nono	Date: 17/02/2007 11:02:02	Points: 20	Status: Answered Quality : Excellent
Hey there, I am inserting a new row into a table where the record_id is an autoincrementing field. I have other data that has to be inserted into other tables with the record_id for that row, but don't know how to grab the new id to use to insert the rows in the other tables...would I just use "SELECT record_id FROM table ORDER BY record_id DESC LIMIT 0,1;" Thanks in advance...
By: Bernard	Date: 17/02/2007 11:02:34		Type : Comment
SELECT MAX(record_id) FROM table;

By: VGR	Date: 17/02/2007 11:04:13		Type : Comment
yo. classical problem. the *theoretical* solution is to use mysql_last_insert_id() function from PHP, *but* you could just as well rely - as I do - on the select you provided : select id from yourtable order by id desc limit 1;". Moreover, it's faster than the max() computation. You can just say "LIMIT 1" by the way. This said, to be honest you run the risk of a second replace or insert having taken place between YOUR insert and YOUR select, as your two operations are not atomical. You can either use a TRANSACTION, or just LOCK the table if you really want to ensure 0 risk (a too high price to pay IMHO for classical webservers doing 99% selects and 1% updates/inserts/replaces/deletes and having medium traffic & activity) best regards

By: Bernard	Date: 17/02/2007 11:04:39		Type : Comment
ust to add to the already good advice, MySQL 5+ now has @@IDENTITY like other SQL databases. You should be able to execute a multiple query like this: "INSERT INTO foo (auto,text) VALUES(NULL,'text'); SELECT @@IDENTITY;" Which is worthless from PHP, by the way.

By: VGR	Date: 17/02/2007 11:05:12		Type : Answer
to complete what my colleague wrote, you have since MySql 3.23.x the MySql function LAST_INSERT_ID() that could be used in a multi-statement query like above. the LAST_INSERT_ID() type-of function is not a SQL-99 requirement, even optional, and is not supported by any other major RDBMS vendor, at least under a function form (there may be "macros" like the @@identity above, a bit like PL/SQL extensions to Oracle, available only in the console/TOAD and not at the API level). I only checked postgresql, oracle, mysql, sql-server for SQL-99 features IDs B031 to P003 , so I may have missed something. Aaaah sorry last minute modification : SQL-Server has a ident_current(`table_name') function as a vendor extension to SQL-99

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 300.711870 milliseconds

Why Google AdSense ads ?

New Improved Search!