Populating Junction Tables

Question

I'm working on a scheduling system were students would be able to book lessons with teachers in php and mysql.

There are 3 separate tables to hold the data and each has their own unique ids studentID, lessonID and teacherID. They are all Many to Many relationships. Many students can have many lessons. Many teachers can have many lessons. As such, there are two junction tables linking the students and teachers to the same lessons, Lessons/Students and Lessons/Teachers.

The problem I am running into is the I am making a form to input a new lesson. I want to be able to assign students and teachers in the same form. Most of the data in the form posts directly to the lessons table which is easy (start time, end time, room number, etc), but I'm running into trouble with the junction table.

I am pulling a list of the available students and teachers and intend to input them into the junction tables based on their IDs. I can't seem to figure out how I am supposed to go about capturing the lesson ID BEFORE it is actually created, as I need the lessonID for the junction table, but the lesson hasn't even been created yet.

All IDs auto increment. The only solution I can think of is to return the latest lessonID and increment by one. Is this a best practice? I get the feeling this might cause problems later. For example in situations when two users try to create lessons at the same time, or there is a delay and another lesson is created before another but they both post the same lesson ID to the junction table.

Any input is appreciated! Thanks

Answer 1

When you insert the lesson record you can retrieve the ID it was assigned (Since you've autoincremented it). So the procedure should be to first insert the lesson, returning the inserted ID. Use this returned lesson ID with the student IDs and teacher IDs to insert the junction records.

// $db is the mysqli object that has been connected to your database

// Insert the lesson
if ($db->query("INSERT INTO .........."))
{
    $lessonID = $db->insert_id;

    // Insert the student and teacher junctions with $lessonID
}

Answer 2

AS you said

For example in situations when two users try to create lessons at the same time

if this happens it does not creates any problem so don't worry about it

Answer 3

You dont have to worry about the lesson doesn't exists as you are posting everything in the same form. You just have to control your logic when you insert the data. So you make submit the data for the lesson and a list of teachers and a list of students. In your code you will have to insert the lesson first (controlling of course any error that could happen) and with the ID of the lesson you can insert the teachers and the students. As said before in the answer befor when you insert you can have the inserted id.