I am setting an auto-increment field in one table and getting it into another using the last_insert_ID command.

I later want to print the value of the last_insert_ID and was advised to use printf (.....mysql_insertID()):

This only returns a 0 value.

Can anyone help me on this?

Cheers

    Define "later". You should be calling mysql_insert_id immediately after executing the query which creates the id you're looking for. To quote the manual

    Return Values

    The ID generated for an AUTO_INCREMENT column by the previous INSERT query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.

      I use the following code to enter the data into the tables, and use last_insert_id() to get the auto-incremetned number that is BOOKING_ID

      $guestquery =("INSERT INTO Guests VALUES ('$Guest_ID','$surname','$initials','$address1',
'$address2','$postcode','$tel_no', '$email_addy')");

$bookingquery =("INSERT INTO Bookings VALUES ('$BOOKING_ID','$Guests_Guest_ID','$NO_OF_NIGHTS','$DATE_FROM','$DATE_TO','$NO_OF_PEOPLE')");

$roomtypequery=("INSERT INTO Rooms  VALUES ('$Bookings_Guests_Guest_ID',LAST_INSERT_ID(),
'$Room_Type')");

      After checking and getting a confirmation that the data has been successfully entered I use this code

      printf("Your booking reference number is <b>%d\n</b>",mysql_insert_id());

      It is here that the value is 0.

        From the PHP Manual:
        mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the query that generates the value.

          Thanks for your input.

          I can get the reference number returned and printed if I don't do another query first.

          I have tried to set a variable to take the data into, so that I can re-use it later on.

          $guestquery =("INSERT INTO Guests VALUES ('$Guest_ID','$surname','$initials','$address1','$address2','$postcode','$tel_no', '$email_addy')");

$bookingquery =("INSERT INTO Bookings VALUES ('$BOOKING_ID','$Guests_Guest_ID','$NO_OF_NIGHTS','$DATE_FROM','$DATE_TO','$NO_OF_PEOPLE')");

$refID=mysql_insert_id();
echo "this is my ref: $refID";

          but this doesn't give me the value, just returns zero.

          I am obviously missing something here. ...

          In the code above, I am setting $refID to equal the mysql_insert_id() value (I think??) can I then use the $refID as value for input in the third table.

          As you can tell I am very new to this, so don't really know what I am doing.

          Cheers
          Harlequeen

            This section is only assigning the sql text to variables. At some point you must do something like

            $result = mysql_query($bookingquery);

            It's after this statement that you want to do mysql_insert_id.

            If you create your next sql statement after getting the insert id, then you can use it in that statement in place of LAST_INSERT_ID(). I wouldn't bother though. It amounts to the same thing in the end, and it looks like you've separated your query creation from execution, so it doesn't appear to fit in your logic flow.

              Thanks for all your help.

              It is working fine now, and doing exactly what I want.

              Cheers everyone

                Write a Reply...