mySQL: insert values into 2 tables with foreign key relationship

Question

I have created 2 tables with the following structure:

mitarbeiter
==================
maID (PK, AUTO_INCREMENT, NOT NULL)
maAnrede
maName
maVname
maDurchwahl
maEmail
maMobilfunkNr
maKartenanzahl
maFirma

mobilfunkkarten
==============================
mfkID (PK, AUTO_INCREMENT, NOT NULL)
mfkTarif
mfkStatus
mfkKartennr
mfkPin
mfkSuperpin
maID(FK)

Now I would like the web user to type in values into form fields. After clicking the "Save"-Button, the data will be saved into the corresponding 2 tables. My mySQL-Query looks like this:

INSERT INTO mitarbeiter,mobilfunkkarten
(maAnrede, maVname, maName, maMobilfunkNr, mfkTarif, maKartenanzahl, mfkStatus, mfkkartennr, mfkPin, mfkSuperpin, maEmail, maFirma) 
VALUES($anrede, $Vorname, $Nachname,.......);

Unfortunately, the query doesn't work because you can't use 2 tables after the INSERT command like in my example.

Any solutions on how to solve this? I just can't think of any way on how to make sure that anything the user types into the form fields will be saved as 1 dataset into these 2 tables, i.e. the NEW data in the child table 'mobilfunkkarten' will be related to the Primary Key Number in the parent table 'mitarbeiter'.

mitarbeiter = workers mobilfunkkarten = mobile phone cards (SIM cards)

It will take two queries because you need to get the key from the related row to insert into the parent row. You can do this in a transaction so that if something fails, you can rollback the insert. — Sloan Thrasher, Jul 06 '18 at 12:50
Also, be sure to use parameterized queries with either mysqli or PDO functions to avoid SQL injection and to properly handle quotes. — Sloan Thrasher, Jul 06 '18 at 12:51
See [this question](https://stackoverflow.com/questions/897356/php-mysql-insert-row-then-get-id) on how to get the primary key back from an INSERT — Joakim Danielson, Jul 06 '18 at 12:51
@SloanThrasher: First of all, thank you. Please don't let us worry about the SQL injection, though. I am using a templating engine that does all the work for me. All I need is the SQL query syntax for my problem mentioned above. — timunix, Jul 06 '18 at 13:02

sticky bit · Accepted Answer · 2018-07-06T13:57:26.240

0

First insert the employees.

INSERT INTO mitarbeiter
            (maanrede,
             ...)
            VALUES(?,
                   ...);

Then insert the SIM card. Use last_insert_id() to get the ID created for the employee record.

INSERT INTO mobilfunkkarten
            (mfktarif,
             ...,
             maID)
            VALUES (?,
                    ...,
                    last_insert_id());

edited Jul 06 '18 at 13:57

answered Jul 06 '18 at 12:58

sticky bit

36,626
12
31
42

This one worked for me. Thanks a lot! Perhaps you should remove/delete unnecessary code like "maMobilfunkNr, mfkTarif,...." in your post to round it up. – timunix Jul 06 '18 at 13:27

mySQL: insert values into 2 tables with foreign key relationship

1 Answers1

Linked