查找多个插入查询的auto_increment值

Question

I have a form to enter a student's name and his marks.

---

I have two tables :

student

student_id | student_name
         1 | John
         2 | Doe

marks

student_id | mark
         1 | 80
         2 | 90

---

The Student Name field has an autocomplete function which checks the student table if the student already exists.

Now, if I type J in the Student Name field in the form, it shows John. If I click John, and then enter a mark(say 50) in the Mark field and submit the form, the student table remain untouched, while the marks table gets inserted with a new row (1 | 50), where 1 is the student_id of the selected student (In this case, id of John).

Now, if I type a new name (say, Rae) in the Student Name field, and some mark(say 75) in the Mark field, both tables should get inserted with a new row on form submit. i.e., student table should now have a new row (3 | Rae) and marks table should have a new row (3 | 75)

MY PROBLEM IS, since student_id is an auto_incremented value from student, the student_id field in the marks table get a value 0 while entering a new student (In this case, Rae), which insert the row as (0 | 75) instead of my requirement (3 | 75).

As far as I know, mysqli_insert_id() can be used here, but I'm using three insert statements in a go. The above mentioned two insert queries are second and third. The auto_increment value from the first insert is being used for some other purpose using the mysqli_insert_id() command.

Answer 1

Use LAST_INSERT_ID() within your query to get the new ID. For example:

INSERT INTO student (student_name) VALUES ('John');
INSERT INTO marks (student_id, mark) VALUES (LAST_INSERT_ID(), 74);