Roger Baklund wrote:
* Scott Plumlee
I've got two separate tables, each with id fields that are auto-increment. The created fields below are timestamps. The tables are Innodb tables using transactions to process the statements. This will be an online registration process for our business, using PHP and MySQL. PHP is using session ids for tracking state.
table1 -------- id first_name last_name created etc.....
table2 --------- id table1_id created etc....
I need to insert a row into table1, using a null value for the id so it generate an id automatically. Then I need to insert a row into table2, including the id from table 1 (table1.id needs to be inserted into table2.table1_id).
Any best practices to doing this?
Use the LAST_INSERT_ID() function:
INSERT INTO table1 SET first_name='roger',last_name='baklund'; INSERT INTO table2 SET table1_id=LAST_INSERT_ID(),etc='whatnot';
This function is connection specific, you will get the correct id even if you have multiple simultaneous users.
<URL: http://www.mysql.com/doc/en/ANSI_diff_Transactions.html#IDX141 > <URL: http://www.mysql.com/doc/en/Information_functions.html#IDX1428 >
-- Roger
--
Scott Plumlee
PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
