What advise, how to find out which number will be assigned to the id field in the database when adding a new id. I add new data from the form to the table, then I need to transfer to another page with the id that the system assigned, is there any solution that allows you to get this information in php?
1 answer
If you use auto_increment as an identifier, you can get its value using the LAST_INSERT_ID() function and the corresponding query select last_insert_id(); .
- When working with mysql through the outdated
mysql_*extension, you can use themysql_insert_id - When working with a database using
mysqliyou can use themysqli::$insert_idor themysqli_insert_id()function - when working through PDO, you can use
PDO::lastInsertId()
teran teran
18k one 13 thirty
- In this case, you will get the id of the last added record in the table, and not the id of the record you just added. In the general case, these are different records, because during the time of the repeated access to the database, several more transactions could be executed to obtain the LAST_INSERT_ID. In my opinion, if you work in two requests, you must first ask the generator the next ID, and then use it with the INSERT. If in a single query, then do INSERT using a stored procedure on the database side that encapsulates the same logic. Saving unnecessary network access and logic in place. - iTollu
- In the comment, the limit on the number of characters, so everything did not fit. To test the stated hypothesis, it suffices to conduct load testing with hundreds of simultaneous requests. - iTollu
- one@iTollu I think you should be able in your code to know if other simultaneous requests are being executed in your current connection . Obviously, if the insret operations for the current connection are performed asynchronously, or entail invoking triggers or something else that may affect the return value, then this method is not suitable. But if you have a synchronous code, and immediately after the insert request, you retrieve the resulting ID, then there should be no problem. Several other transactions performed by other users will not affect the result, because it refers to the connection. - teran
- but since Since php in one-threaded, it is not entirely clear to me what other transactions can occur between two consecutive requests to
insertand receiveid- teran - It agree, in the MySQL documentation found: The ID that was generated is maintained on the per-connection basis. AUTO_INCREMENT value generated by the most recent statement affecting the AUTO_INCREMENT This value can not be affected by other clients, even if they generate AUTO_INCREMENT values of their own. It ensures that it will ensure that it is not a problem. - iTollu
|