5

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

bilasek
  • 77
  • 2
  • 4
  • 1
    you might want to add the actually "show me the error" code after the r2 call to get the error. (look it up, I'm not sure how it goes so from the top of my head, but you are looking for `or die(mysql_error());` or the likes) – Nanne Jul 31 '12 at 16:46
  • Is your container.container_id an auto_increment PK? Without that, there will NEVER be a last_insert_id(). – Marc B Jul 31 '12 at 16:51
  • @MarcB He made mention that container_id is auto incremented. `container_id INT (PK AI)`, PK stands for primary key, AI stands for auto increment. – Mihai Stancu Jul 31 '12 at 16:52
  • @mihai: I see no mention of auto_increment other than my question. – Marc B Jul 31 '12 at 16:53
  • sorry friends! The code above is correct. I have made syntax mistake in code in my project. – bilasek Jul 31 '12 at 17:00

1 Answers1

4

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.

Mihai Stancu
  • 15,848
  • 2
  • 33
  • 51