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 - reservation

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

The 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 I did wrong? $r2 return false. LAST_INSERT_ID() not works

Answer 1

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

You problem is definitely not LAST_INSERT_ID() but more likely any other part of your code is failing.

Try checking if there was an error and outputting the error to be able to read the error message and act accordingly.

Use mysql_error() for that.