stevolicious
asked on
php,mysqli, and last_insert_id()
using PHP 5, mysqli extension, and mysql 4.1.3: according to the mysql documentation, last_insert_id returns the last autoincremented value from an insert OR update. ...but, how would you get an update query to autoincrement?
When I set an autoincrement field to a number I created, insert_id() returns 0.
Q: I need to get an updated record's primary key without having to submit a subsequent SELECT statement. Allowing two consecutive statements (UPDATE, then SELECT) might allow another UPDATE to occur (in a multiuser environment) before the Select statement could be called, returning the second record's primary key value instead. ...and 'no', I don't know the PK value I just updated, because my statement was 'UPDATE PK SET PK = PK+1 WHERE 1 LIMIT 1'
Reason: I have a single field , single value table I use to maintain a counter.
When I set an autoincrement field to a number I created, insert_id() returns 0.
Q: I need to get an updated record's primary key without having to submit a subsequent SELECT statement. Allowing two consecutive statements (UPDATE, then SELECT) might allow another UPDATE to occur (in a multiuser environment) before the Select statement could be called, returning the second record's primary key value instead. ...and 'no', I don't know the PK value I just updated, because my statement was 'UPDATE PK SET PK = PK+1 WHERE 1 LIMIT 1'
Reason: I have a single field , single value table I use to maintain a counter.
MySQL guarantees that it will return the correct PK value of an insert even if there are simultaneous inserts occurring. The only thing you have to be sure of is that you use the same connection to the database for both the insert and then the SELECT LAST_INSERT_ID(). So if you obtain a connection, insert some data and then query for the last insert id before closing that connection you will always get the correct PK.
ASKER
...but what if the last statement was an update that sets the PK field to PK+1 (not autoincremented, not an insert). Is there an update statement that would autoincrement the PK?...and does last_insert_id() truly work with updates as mentioned in the documentation?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.