[PHP-DB] Re: Get Last ID Inserted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Greetings Adam On 21 November 2002 at 11:12:46 -0500 (which was 16:12 where I live) Adam Voigt emanated these words of wisdom But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? No unless you drop the connection, but then it should give you a NULL. To quote M$ ==8= Remarks IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ. IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. ==8= - -- Ti2GO,___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | I didn't do it! Nobody saw me do it! You can't prove anything!| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd3zufmK8eZlD0U0EQJ37QCglnr+5j0h7CSOG39qznRq2F8fZNIAn10R xhMTzYhlrvim4iyzB0rny48J =DtzT -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Get Last ID Inserted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Get Last ID Inserted
But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? On Thu, 2002-11-21 at 11:03, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
RE: [PHP-DB] Re: Get Last ID Inserted
I do see that picking up the incorrect ID, which is why we created a stored procedure which returned the affected row. -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 11:13 AM To: David Elliott Cc: Adam Voigt on PHP-DB Subject: Re: [PHP-DB] Re: Get Last ID Inserted But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? On Thu, 2002-11-21 at 11:03, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer | _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner | -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Get Last ID Inserted
Please don't ban more for this example using VB, but this is how I did it a while ago in VB/ASP, generating and setting the RecID in one stroke. I assume you could just wrap this in PHP and it would work just as well. strSQL=Select newid() RecID = objConn.Execute(strSQL)(0).value Ryan Marrs wrote: I do see that picking up the incorrect ID, which is why we created a stored procedure which returned the affected row. -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 11:13 AM To: David Elliott Cc: Adam Voigt on PHP-DB Subject: Re: [PHP-DB] Re: Get Last ID Inserted But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? On Thu, 2002-11-21 at 11:03, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer | _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner | -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Get Last ID Inserted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 11:12:46 -0500 (which was 16:12 where I live) Adam Voigt rearranged electrons to get But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? No. It picks up the last identity on that connection. So it does not mater how many other connections and what they are doing. It can only be wrong if you close off the connection and open another one. The other way is to put it all into one SQL statement (as long as you don't use mssql_? functions in PHP) e.g. (one I used earlier) ==8= begin transaction set nocount on declare @NewId int update item set name = 'Canada Delivery' where itemid = '428' insert into item (ItemTypeId, Name, LastUpdated) values (49,'Mexico Delivery',getutcdate()) set @NewId = @@identity insert into DelArea (itemid,StdEUR, StdGBP, StdUSD, HotEUR, HotGBP, HotUSD) values (@NewId,37,19,45,134,70,70) Update country set DelId = @NewId where itemid = 288 commit select @NewId NewDelId ==8= - -- Ti2GO,___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | Do you think someone was BORGED when they made these up?| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0TBPmK8eZlD0U0EQL1awCgmS57QwyvS+bz02XsLQtwJOSGSB8AoLmo I9VdgNIsp3GXkzWAX6I1jYpp =UXBQ -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Get Last ID Inserted
Ahh, thanks very much. On Thu, 2002-11-21 at 12:08, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 11:12:46 -0500 (which was 16:12 where I live) Adam Voigt rearranged electrons to get But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? No. It picks up the last identity on that connection. So it does not mater how many other connections and what they are doing. It can only be wrong if you close off the connection and open another one. The other way is to put it all into one SQL statement (as long as you don't use mssql_? functions in PHP) e.g. (one I used earlier) ==8= begin transaction set nocount on declare @NewId int update item set name = 'Canada Delivery' where itemid = '428' insert into item (ItemTypeId, Name, LastUpdated) values (49,'Mexico Delivery',getutcdate()) set @NewId = @@identity insert into DelArea (itemid,StdEUR, StdGBP, StdUSD, HotEUR, HotGBP, HotUSD) values (@NewId,37,19,45,134,70,70) Update country set DelId = @NewId where itemid = 288 commit select @NewId NewDelId ==8= - -- Ti2GO,___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | Do you think someone was BORGED when they made these up?| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0TBPmK8eZlD0U0EQL1awCgmS57QwyvS+bz02XsLQtwJOSGSB8AoLmo I9VdgNIsp3GXkzWAX6I1jYpp =UXBQ -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
RE: [PHP-DB] Re: Get Last ID Inserted
From the MySQL docs: The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). So as long as you're using non-persistent connections, even on a site with heavy traffic it should work fine. -Micah At 11:14 AM 11/21/2002 -0500, you wrote: I do see that picking up the incorrect ID, which is why we created a stored procedure which returned the affected row. -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 11:13 AM To: David Elliott Cc: Adam Voigt on PHP-DB Subject: Re: [PHP-DB] Re: Get Last ID Inserted But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? On Thu, 2002-11-21 at 11:03, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer | _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner | -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
