MySQL Procedure to insert data to a table that use auto_increment, get the PK auto_increment generated and insert it into a bridgetable?

Question

I´m creating a restApi with PHP over the courses I have studied. When it comes to the database I´m not sure whats the best practise for this problem ->

I have data over the languages each course had, to normalize data I have languages in a separate table and a bridge to connect them.

So one table for Courses, one for Languages and one bridge table to connect them.

CREATE TABLE `Courses` 
(`Course_ID` INT(11),
`Education_ID` INT(11),
`CourseName` VARCHAR(100) NOT NULL,
`Points` VARCHAR (5),
`Grade` VARCHAR(3), 
PRIMARY KEY (`Course_ID`)
);


CREATE TABLE `Language` (
  `Language_ID` INT(11),
  `Language` VARCHAR(100) NOT NULL,
  `Img_url` VARCHAR (200),
  PRIMARY KEY (`Language_ID`)
);
CREATE TABLE `Bridge_language` (
  `Course_ID` INT(11) NOT NULL,
  `Language_ID` INT(11) NOT NULL,
  KEY `PKFK` (`Course_ID`, `Language_ID`)
);
ALTER TABLE Courses MODIFY Course_ID INTEGER AUTO_INCREMENT;
ALTER TABLE Language MODIFY Language_ID INTEGER AUTO_INCREMENT;

When adding a new course, in the SQL I know the id of the languages, (i will have a function in the admin page where you add new languages) then when you create a new course you just click add languages and the id for the language is added.

But what I don't have is the ID for the course which is created with auto_increment. Is there a smart way you with a function/procedure in SQL, can grab the id that auto_increment has generated and use it to add that into the bridge table?

Or do I need to make a query to the database and grab the latest ID and add one and send that into the bridge table?

Answer 1

In MySQL, you can use last_insert_id() to retrieve the auto-generated id of the last insert query that you executed. You don't give much details about your code, but the logic is like:

insert into course (education_id, coursename, points, grade)
values (?, ?, ?, ?);

insert into bridge_language (course_id, language_id)
values (last_insert_id(), ?);