CODEWITHSUNDEEP
phpsqlauto-increment
Kaptah
Kaptah

Reputation: 9899

SQL - INSERT and catch the id auto-increment value

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?

Upvotes: 17

Views: 50851

Answers (7)

Omar Qureshi
Omar Qureshi

Reputation: 9093

In postgres the best way is to do something like:

insert into foos(name) values ('my_foo') returning id;

Upvotes: 8

HLGEM
HLGEM

Reputation: 96630

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.

Upvotes: 0

MyItchyChin
MyItchyChin

Reputation: 14041

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

Upvotes: 2

A-K
A-K

Reputation: 17090

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.

Upvotes: 0

Milan Babuškov
Milan Babuškov

Reputation: 61198

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.

Upvotes: 3

superUntitled
superUntitled

Reputation: 22547

In php: mysql_insert_id() https://www.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

Upvotes: 0

Matthew
Matthew

Reputation: 1875

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.

Upvotes: 16

Related Questions