phpmysqlforeign-keyssql-insertmysql-insert-id

Last Inserted respective Records Id from MySQL PHP

I'm having two MySQL Tables Customer and Address. I'm having data of a Complete Customer (i.e., Personal Info with Address)

Table Structures

Customer

CustId    FullName    Gender    AddressId
__________________________________________

Address

AddressId    Street    City    State
_____________________________________

I'm inserting the table values in PHP platform, how could I relate the AddressId and Insert all the data in a Single shot. I can't trust the Last Inserted Id, because its a Web we can't assure the last inserted AddressId is belongs the respective record (Cloud data - Online Insertion)

Solution

  • Last Inserted ID in MySQL is tracked at the connection level (https://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html).

    For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

    This means, unless inside your same connection you do other inserts with unique autoincrement id's using a magic (null or 0) after the insert you're concerned about, you'll be sure to get back the last id created from your prior insert on your specific connection.