[astonecipher]

I am not sure if this in in the correct area or not. I am trying to do an insert statement into two tables. Table one is the parent and has an auto increment field as the key. The child table is suppose to get the key from the parent by using last_insert_id and insert into the child table. But, the child table as it's own auto increment field. So, using the last_insert_id function returns the id of the wrong field. I understand why it's not working, because the last id entered is the question_id field. Do I have to drop the table and rearrange the quiz_id and question_id fields?

    //Insert Quiz information into database
    $query = "INSERT INTO `question` (`quiz_id`,`question_id`, `question`, `a`, `b`, `c`, `d`, `key`)" .
			     // Not entering the correct quiz_id
                            "VALUES ( last_insert_id(), NULL, '$q', '$a', '$b', '$c', '$d', '$key')";

Did I construct the table wrong for this or is there a way around it?

[Atli]

I think you may be misunderstanding how the last_insert_id() function works. It doesn't care how the fields are organized, or what numbers are being generated in this query. It just gives you the number generated by the last insert query executed before this one.

For example, this would work just fine:

// Create a new quiz, which will generate a new AI value for the
// "id" field.
$pdo->query("INSERT INTO `quiz`(`name`)
             VALUES('first')");

// Now insert a question associated with the above quiz.
$pdo->query("INSERT INTO `question`(`question`, `quiz_id`)
             VALUES ('Lorem ipsum #1', last_insert_id())");

The second query would correctly insert the ID generated by the fist query as the "quiz_id" value.

Of course, if you want to insert multiple questions into a quiz, things get a little more complicated. As soon as the second query is executed, the last_insert_id() changes to the value of that query, so executing a third query exactly like the second query won't work. To work around that, you have two options:

• You can fetch the ID from the first query into the PHP code and pass it into the question insert queries like you would any other data. You can do that with functions like PDO::lastInsertId().
  
  
• Or you can use one insert query to insert multiple questions. The last insert ID won't change until after the statement has finished, so it can be used safely multiple times inside the same query. For example, this will insert three questions for the quiz:
  

  $pdo->query("INSERT INTO `quiz`(`name`)
               VALUES('first')");
  
  $pdo->query("INSERT INTO `question`(`question`, `quiz_id`)
               VALUES
                  ('Lorem ipsum #1', last_insert_id()),
                  ('Lorem ipsum #2', last_insert_id()),
                  ('Lorem ipsum #3', last_insert_id())");
  
  

[astonecipher]

Atli, on 06 January 2013 - 09:30 AM, said:

Of course, if you want to insert multiple questions into a quiz, things get a little more complicated. As soon as the second query is executed, the last_insert_id() changes to the value of that query, so executing a third query exactly like the second query won't work. To work around that, you have two options:

• You can fetch the ID from the first query into the PHP code and pass it into the question insert queries like you would any other data. You can do that with functions like PDO::lastInsertId().
  
  
• Or you can use one insert query to insert multiple questions. The last insert ID won't change until after the statement has finished, so it can be used safely multiple times inside the same query. For example, this will insert three questions for the quiz:
  

  $pdo->query("INSERT INTO `quiz`(`name`)
               VALUES('first')");
  
  $pdo->query("INSERT INTO `question`(`question`, `quiz_id`)
               VALUES
                  ('Lorem ipsum #1', last_insert_id()),
                  ('Lorem ipsum #2', last_insert_id()),
                  ('Lorem ipsum #3', last_insert_id())");
  
  

My problem would be I am making multiple insertions.
I am doing a foreach loop to insert the data because the inputs are being dynamically generated.

So, either last insert id is 0 or the question id of the last question inserted. I only want the last id of the quiz_id column not the question_id column.(And I tried using implode but, it returned, values not equal to column total.)

[Atli]

Then you want to use the first method I mentioned. Insert the quiz and then use PDO::lastInsertId() -- or it's equivalent from the other APIs: mysqli::$insert_id or mysql_insert_id() (this last one is deprecated, by the way; not to be used!) -- to fetch it into a PHP variable. Then use the PHP variable as the value for the quiz_id for the question inserts.

[astonecipher]

Thank you. Got it working with the separate select statement inserted into a variable.