I have the following code to get the newest entry from the a table.
$con = mysqli_connect($host,$username,$password,$database);
$result = $con->query("SELECT MAX(ID) FROM Samples");
$newest = $result->fetch_assoc();
The field ID is the primary key and has auto_increment. I have no code to alter the value of ID.
The code always successfully yields the newest entry when I test it, but I don't know if this is behavior I can rely on. Will this always work? If not, is there a more reliable way to get the newest entry?
Will
SELECT MAX(ID) FROM ...always work?
No, it will not. If you have more than one copy of your program connected to your database, and one program does what you suggest, and another program then performs an insert, the first program's MAX(ID) value will become stale. It's a race condition. Those are very difficult to detect and debug, because they don't crop up until a system is under heavy load.
If all you care about is a snapshot of MAX(ID) you'll be OK. But you cannot use this MAX(ID) value as the value for the next insertion, unless you never will have more than one program instance inserting data. It's unwise to rely on that kind of never.
If not, is there a more reliable way to get the newest entry?
Immediately after you do an INSERT operation you can do SELECT LAST_INSERT_ID() to retrieve the autoincrementing ID just used. Various APIs, like mysqli, PDO, and JDBC return this ID to a calling program when an INSERT operation completes.
LAST_INSERT_ID() and the related API functions work on a connection-by-connection basis. Therefore, if multiple connections are inserting rows, each connection will get its own id values.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments