In some cases, you need to know the generated ID after executing the INSERT operation on the database (the primary key of MySQL is AUTO_INCREMENT, when you perform the insert operation, you do not need to specify the ID value and the database will generate it). In this case, you can use the PHP function mysql_insert_id () to directly obtain this ID, which is very convenient.
However, if the INSERT statement explicitly specifies the value of the ID column with the AUTO_INCREMENT attribute, the function returns 0 because the ID value is specified by the user, it is not automatically generated by the database.
The PHP manual describes the function as follows:
Mysql_insert_id ()
The mysql_insert_id () function returns the ID generated by the previous INSERT operation. If no AUTO_INCREMENT ID is generated in the previous query, mysql_insert_id () returns 0.
Syntax: mysql_insert_id (connection)
The connection parameter. Optional. MySQL connection is required. If not specified, use the previous connection.
Mysql_insert_id () returns the AUTO_INCREMENT ID generated in the previous INSERT query in the given connection. If no connection is specified, the last opened connection is used.
If you want to save the value and use it later, make sure that mysql_insert_id () is called immediately after the query that generates the value ().
<?php$con = mysql_connect("localhost", "hello", "321");if (!$con){die('Could not connect: ' . mysql_error());}$db_selected = mysql_select_db("test_db",$con);$sql = "INSERT INTO person VALUES ('Carter','Thomas','Beijing')";$result = mysql_query($sql,$con);echo "ID of last inserted record is: " . mysql_insert_id();mysql_close($con);?>
Mysql_insert_id () returns the AUTO_INCREMENT ID generated in the previous INSERT query in the given link_identifier. If link_identifier is not specified, the last opened connection is used. If no AUTO_INCREMENT value is generated in the previous query, mysql_insert_id () returns 0. If you want to save the value and use it later, make sure that mysql_insert_id () is called immediately after the query that generates the value ().
LAST_INSERT_ID ()
MySQL also provides an API with the same function. It always stores the newly generated AUTO_INCREMENT value and will not be reset between Query statements. That is to say, after the INSERT operation is executed, executing SELECT, UPDATE, and DELETE statements does not affect the return values of this API.
You can use SELECT LAST_INSERT_ID (); to query the return value of LAST_INSERT_ID.
INSERT multiple records using a single INSERT statement. LAST_INSERT_ID () Only returns the AUTO_INCREMENT value generated for the first inserted record.