PHP / MYSQL how to get last_insert_id in transaction

Question

I have 2 MySQL tables

table # 1 - container:

container_id INT (PK AI) start_time DATETIME 

table # 2 - booking

 reservation_id INT (PK AI) reservation_time DATETIME container_id INT (FK) 

PHP code:

 mysqli_query($dbc,'SET AUTOCOMMIT=0'); mysqli_query($dbc,'START TRANSACTION'); $q1 = "INSERT INTO container (start_time) VALUES ('2012-07-03 11:00:00')"; $q2 = "INSERT INTO reservation (reservation_time, container_id) VALUES ('2012-07-03 11:00:00', LAST_INSERT_ID())"; $r1 = mysqli_query($dbc, $q1); $r2 = mysqli_query($dbc, $q2); if ($r1 && r2){ mysqli_query($dbc, 'COMMIT'); } else { mysqli_query($dbc, 'ROLLBACK'); } 

What have I done wrong? $ r2 returns false. LAST_INSERT_ID () does not work

Answer 1

LAST_INSERT_ID() work in any context, be it transactions or user-defined stored procedures or user-defined functions.

The problem, of course, is not LAST_INSERT_ID() , but most likely, any other part of your code does not work.

Try to check if there was an error, and throws an error so that you can read the error message and act accordingly.

Use mysql_error() for this.