The Easiest Way To Insert Relational Data Into MySQL

How did I insert relation data with a single execution?

Photo by Campaign Creators on Unsplash

Sometimes developers have to maintain tables to execute the process smoothly. When it comes to inserting relational data manually, it is a time-consuming task. We have 3 languages in our software and I am one who handles translations. Before telling you that how I insert translation into two tables, I would like to show you table structure quickly,

1. Keys Table

Keys Table Screenshot

1. Translations Table

Translations Table Screenshot

So if I need to add a ‘Sign In’ translation, I would insert it by following queries.

First of all, let’s insert the key into the keys table

INSERT INTO keys(`key`, `group`) VALUES (‘login_heading’, ‘HeadingGroup’);

Then save last inserted key ID into MySQL variable,

SET @lastInsertedID = LAST_INSERT_ID();

Now we have lastInsertedIDas key_id and we can use it in further queries. Let’s insert a translation into the translationstable on behalf of the key that we inserted into the key table.

INSERT INTO `translations` (key_id, language_id, translate) VALUES (@lastInsertedID, 1, ‘Login’);

You can see that we are using lastInsertedIDas key_idin the translations table. Now, all we need to do is translate data and put it into the queries. So following are my queries, which would insert key and its translations with a single process.

/* Insert Key */
INSERT INTO keys(`key`, `group`)
VALUES (‘login_heading’, ‘HeadingGroup’);
/* Store Last Inserted ID */
SET @lastInsertedID = LAST_INSERT_ID();
/* Insert Translations */
INSERT INTO `translations` (key_id, language_id, translate)
VALUES (@lastInsertedID, 1, ‘Login’);
INSERT INTO `translations` (key_id, language_id, translate)
VALUES (@lastInsertedID, 2, ‘Connexion’);
INSERT INTO `translations` (key_id, language_id, translate)
VALUES (@lastInsertedID, 3, ‘Anmeldung’);