With the PHP function mysql_insert_id( ), you can obtain the previous index generated from an insert operation.
The need for this capability came about when we were generating a MySQL database for invoice storage. Our database contains an INVOICE table for customer billing information, and an INV_ITEM table for individual items that are included in each order.
The INVOICE table uses inv_id as an AUTO INCREMENT primary key, and the INV_ITEM uses inv_id as an index value, so the invoice items may be associated with the correct invoice. All of this worked great, but we needed to send an email with order information, and an order number corresponding to the inv_id so we could access the order easily from the database if need be.
The answer was to use the mysql_insert_id( ) function immeadiately after the insert statement, which returns the generated ID, which was inv_id in our case. We then stored the returned ID to a string, and used it where necessary in the rest of our code.
$sql = “INSERT INTO invoices (“.$key_string.”) VALUES (“.$value_string.”)”;
$result = mysql_query($sql) ;
$invoice_id = mysql_insert_id();
if (!$result) {
die(‘Invalid query: ‘ . mysql_error());
}
The result of the previous code was that our values were inserted into the database as required, with the inv_id generated, and then stored into the $invoice_id variable for further use in the code.
