How to get record that has been freshly inserted

[dazman]

Hi,

 

I am inserting a record into mysql with PHP but in the customer table, it auto increments. So I dont actually know what the customer number is going to be until I have written the record. What i need to do is get the customer number (PK) after the record is entered into the database. 

 

Here is my code

//start insert transaction
$starttransaction = mysql_query("START TRANSACTION") or die (mysql_error($error));
//insert address into addresstable. 
$insertaddress = mysql_query("INSERT INTO address (Address_ID, Address_Line1, Address_Line2, Address_Line3, ZipPostalCode, City, State, Country) VALUES ('$ADIDendnumber', '{$_POST['Address_Line1']}', '{$_POST['Address_Line2']}','{$_POST['Address_Line3']}', '{$_POST['ZipPostalCode']}', '{$_POST['City']}', '{$_POST['State']}', '{$_POST['Country']}')")or die (mysql_error($error));

//insert customer into customertable.
$insertcustomer = mysql_query("INSERT INTO customer (Cust_Name, Cust_Phone, Cust_Fax, Address_ID, Cust_Email, Cust_Website, Cust_Climit, Tax_Code, Cust_Group, Cust_PaymentTerms, Cust_BankAccountNo) VALUES ('{$_POST['Cust_Name']}','{$_POST['Cust_Phone']}','{$_POST['Cust_Fax']}','$ADIDendnumber','{$_POST['Cust_Email']}','{$_POST['Cust_Website']}','{$_POST['Cust_Climit']}','{$_POST['Tax_Code']}', '{$_POST['Cust_Group']}','{$_POST['Cust_PaymentTerms']}','{$_POST['Cust_BankAccountNo']}')")or die (mysql_error($error));

//check for errors ($error)
echo $error;
if (isset($error))	{
$rollback = mysql_query("ROLLBACK");
echo $error;
echo ("transaction failed");
} 
$commit = mysql_query("COMMIT");

 

And here is the table information:

mysql> show columns in customer;;

+----------------------+------------------+------+-----+---------+----------------+

| Field                | Type            | Null | Key | Default | Extra          |

+----------------------+------------------+------+-----+---------+----------------+

| Cust_Number          | int(10) unsigned | NO  | PRI | NULL    | auto_increment |

| Cust_Name            | varchar(45)      | NO  |    | NULL    |                |

| Cust_Phone          | varchar(20)      | NO  |    | NULL    |                |

| Cust_Fax            | varchar(20)      | NO  |    | NULL    |                |

| Address_ID          | varchar(12)      | NO  | MUL | NULL    |                |

| Cust_Email          | varchar(45)      | NO  |    | NULL    |                |

| Cust_Website        | varchar(100)    | NO  |    | NULL    |                |

| Cust_Climit          | decimal(12,2)    | NO  |    | NULL    |                |

| Tax_Code            | varchar(5)      | NO  | MUL | NULL    |                |

| Cust_Invoice_Account | int(10) unsigned | NO  |    | NULL    |                |

| Cust_Group          | varchar(45)      | NO  | MUL | NULL    |                |

| Cust_PaymentTerms    | varchar(45)      | NO  |    | NULL    |                |

| Cust_BankAccountNo  | varchar(20)      | NO  |    | NULL    |                |

+----------------------+------------------+------+-----+---------+----------------+

13 rows in set (0.00 sec)

 

Is there any other way of finding out this apart from doing a horrid type of select statement after i have done the insert?

 

What is the best way to rectify this problem.

 

Thanks

Daz

[corbin]

mysql_insert_id

 

 

Not sure if it works with transactions though.

 

 

If it doesn't, you could try:

 

 

SELECT LAST_INSERT_ID()

[dazman]

Thats awesome.. thanks heaps. The mysql_insert_id() worked on the transaction. I will need to test that it rolls back correctly, as I have done it in between the start and and the commit. But I imagine the rollback will go right back to the start.. xD.