php - mySQL: insert values into 2 tables with foreign key relationship

299

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)

823

Answer

Solution:

First insert the employees.

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

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

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

People are also looking for solutions to the problem: php - Argument 1 passed to __construct() must be an instance of GuzzleHttpClient

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.