Asked  7 Months ago    Answers:  5   Viewed   29 times

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?

 Answers

72

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.

Wednesday, March 31, 2021
 
tika
answered 7 Months ago
85

No, I don't think this is possible.

You can create a UNIQUE INDEX which has essentially the same effect as a PRIMARY KEY:

CREATE UNIQUE INDEX pk_index ON "table1"("field1","field2");

Besides, I fail to see the logic of your schema, that is -> if a column is autoincrement and you don't intend to mess with the values manually, it's going to be unique anyway, so it makes a good simple short primary key. Why the composite? You may have good reasons to make another index on the combination of columns, though.

Wednesday, July 28, 2021
 
nomie
answered 3 Months ago
91

Run this in your MySQL:

ALTER TABLE `mytable` 
CHANGE COLUMN `DateTime` `DateTime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ;

This sets your default value to CURRENT_TIMESTAMP.

Saturday, August 14, 2021
 
123r789
answered 3 Months ago
89

Through plain SQL:

 insert into language(name) values ('value');
 SELECT IDENTITY_VAL_LOCAL();

See the manual for details: http://db.apache.org/derby/docs/10.7/ref/rrefidentityvallocal.html

When doing this from a Java class (through JDBC) you can use getGeneratedKeys() after "requesting" them with the approriate executeUpdate() method.

Thursday, August 26, 2021
 
Slinky
answered 2 Months ago
84

The primary key for SQLite tables is called _id. It is auto incrementing, and you should not be trying to insert values into it.

gamesdatabase = openOrCreateDatabase("GamesDatabase", MODE_PRIVATE, null);
gamesdatabase.execSQL("CREATE TABLE IF NOT EXISTS Games (_id INTEGER PRIMARY KEY, Name
VARACHAR, NPlayers INT(1), NRounds INT(2), WinScore INT(2));");

gamesdatabase.execSQL("INSERT INTO Games 
(Name, NPlayers, NRounds, WinScore ) VALUES ('TAWLA',2,0,0 );");

gamesdatabase.execSQL("INSERT INTO Games 
(Name, NPlayers, NRounds, WinScore ) VALUES ('DOMANA',4,0,0 );");


Cursor c = gamesdatabase.rawQuery("SELECT * FROM Games", null);
c.moveToFirst();
while (c.isAfterLast() == false) {
Log.d("BEZRA", String.valueOf(c.getInt(c.getColumnIndex("_id"))));
c.moveToNext();
}
Monday, September 20, 2021
 
Amalina Aziz
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :