What is the best way to get the auto-id value in the same SQL with a SELECT?
A forum said adding this "; has Return Scope_Identity()"
in the end of the SQL works in ASP.
Is there a corresponding way in PHP?
views:
10639answers:
7
A:
In php: mysql_insert_id() http://us3.php.net/mysql_insert_id
or
If you wanted to genterate the number from your mySql select query, you could use this EDIT:
SELECT LAST_INSERT_ID(`1`) + 1 FROM table
superUntitled
2009-03-07 05:32:52
Selecting the max could be an issue if multiple processes are doing inserts could it not?
schooner
2009-03-07 10:38:18
if you insert a record, then delete a record, then run this query you will get the identity of the deleted record. the next insert will not get that identity in any implementation I'm aware of. Avoid this at all costs...
Dems
2009-03-07 15:11:25
you should put a read lock on the table if you want this to be safe! even then, I'm not sure that the DBMS won't re-use some deleted id if a gap in the table exists somewhere.
ʞɔıu
2009-03-07 17:50:24
thanks for the warning, i will look into the read lock
superUntitled
2009-03-08 04:44:48
+8
A:
It depends on your database server. Using MySQL, call mysql_insert_id() immediately after your insert query. Using PostgreSQL, first query "select nextval(seq)" on the sequence and include the key in your insert query.
Querying for "select max(id) + 1 from tbl" could fail if another request inserts a record simultaneously.
Matthew
2009-03-07 05:38:58
A:
It depends on the database engine you are using. Some DBMS, like Firebird for example, have RETURNING clause you can add to your query. For example, if you have a table named TABLE1 with autoincrement column named ID, you can use this:
insert into TABLE1(columns...) values (values...) returning ID;
And it would return the inserted ID just like a regular select statement.
Milan Babuškov
2009-03-07 17:03:13
A:
Be very careful: Apparently select nextval(seq) does not work in high concurrency - some other connection can insert between the time when you inserted and the time when you called select nextval(seq). Always test such code in high concurrency test harnesses.
AlexKuznetsov
2009-07-10 13:44:55
This doesn't matter unless you are using the ID for ordering, because you are using the result from nextval(seq), not another call to the sequence itself.
Brian Ramsay
2010-04-25 19:11:49
A:
In Microsoft Transact SQL you can use @@IDENTITY.
e.g.
DECLARE @Table TABLE ( col0 INT IDENTITY, col1 VARCHAR(255), col2 VARCHAR(255))
INSERT INTO @Table (col1, col2) VALUES ('Hello','World!')
SELECT @@Identity
SELECT * FROM @Table
CptSkippy
2009-07-10 13:47:46
never use @@identity, it wil not always give you the correct identity value and can cause very bad data integrity problems. Use scope_identity()
HLGEM
2009-07-10 13:50:47
Well, @@identity is ok as long as you know what it does and the scope of its effect. Just be careful if you have triggers on tables (an insert to a table with an identity column in a trigger will cause @@identity to be updated), or if the table is replicated. Ok, it's probably safer to use scope_identity() :)
Jon
2009-07-10 13:57:22
A:
In SQL Server a insert using the select statement can have an output clause which will return the identity value and whatever other columns you might need to identify which identity goes to which record. If you are using a values clause, then use select scope_identity () immediately after the insert.
HLGEM
2009-07-10 13:51:44
+1
A:
In postgres the best way is to do something like:
insert into foos(name) values ('my_foo') returning id;
Omar Qureshi
2009-09-20 22:04:50