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.
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);