CODEWITHSUNDEEP
mysqlsqlselect
sgtBear
sgtBear

Reputation: 61

MySQL select after insert

Lets asume a table like:

ID = INT, AutoIncrement

VAR = VARCHAR, 65

FOO = VARCHAR, 65

I want to insert something new, and get the Auto-Generated ID, for further use.

My current solution is:

1.INSERT into table (VAR,FOO) VALUES ('test','anothertest')

2.SELECT * FROM table ORDER BY ID LIMIT 1

To get the last insert, but the problem is, what happends if the website lags, and there is a time gap between the queries?

example:

  1. 12:00AM 0.000s -> "INSERT into table (VAR,FOO) VALUES ('test','anothertest')
  2. 12:00AM 0.500s -> "INSERT into table (VAR,FOO) VALUES ('xyz', '!!!!!)`
  3. 12:00AM 0.800s -> "SELECT * FROM table ORDER BY ID LIMIT 1

the Query in 3 would not return the ID from 1, it would return the ID from 2

My Question no is, is there an absolute secure way to get a Value from a inserted Query?

like a confirmation "test, anothertest has successfully inserted into table, ID is 20"

the ID should be available as variable in php, just for information

Upvotes: 0

Views: 4116

Answers (3)

Alexey Kurilov
Alexey Kurilov

Reputation: 438

In php I use mysql_insert_id();

In later MySql insert/update I use LAST_INSERT_ID()

It's absolutely the same.

Upvotes: 1

Josh Daugherty
Josh Daugherty

Reputation: 1

Try using, Scope_Identity(). This returns the last autogenerated id.

This link has more info: http://msdn.microsoft.com/en-us/library/ms190315.aspx

Upvotes: -1

Adam
Adam

Reputation: 6763

Since you mention PHP there is usually a specific function to get that. If you are using the mysqli drivers then see mysqli_insert_id

Edit: According to the docs linked above The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value. Since it is talking about queries on the connection then I would interpret that as meaning that queries on other connection (i.e. queries from other requests) won't affect the value returned. As long as you call it dirctly after the insert (before you do anythign else) then it should work.

Caveat: I am simply interpreting the docs here. I haven't actually tested for other calls myself. Wouldn't be that difficult though - simply have a script which does an insert, a sleep then fetches the ID, giving you time to do another insert during the sleep.

Upvotes: 1

Related Questions