mySQL: UPDATE statement for 2 tables with a foreign key relation

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. (I will let him edit his/her entries there, which will be saved in the mysql-database. So these entries are NOT new!) After clicking the "Save"-Button, the data will be saved into the corresponding 2 tables. My mySQL-Query looks like this (I am using symfony's php templating engine "twig"):

DatabaseLink::getInstance()->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if(isset($_POST["btnSaveMfk"]))
{
$stmt = DatabaseLink::getInstance()->prepare("UPDATE mitarbeiter SET 
maAnrede = :maAnrede, 
maVname = :maVname, 
maName = :maName, 
maMobilfunkNr = :maMobilfunkNr,
maKartenanzahl = :maKartenanzahl,
maEmail = :maEmail,
maFirma = :maFirma
WHERE mitarbeiter.maID = :maid;
UPDATE mobilfunkkarten SET mfkTarif = :mfkTarif,
mfkStatus = :mfkStatus, 
mfkPin = :mfkPin,
mfkSuperpin = :mfkSuperpin");

$status = $stmt->execute(array(

":maid" => $_POST["txtMaId"],
":maAnrede" => $_POST["txtAnrede"],
..................,
..................,
..................
)); 
header("Location: Mobilfunkdaten"); //back to the PHP table that shows all entries
}

I believe that this won't work because the 2 tables are related with a foreign key and if I update both tables without this relation, the statement will result in an error or it will overwrite something unrelated. Am I right with this assumption?

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 UPDATED 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)

Possible duplicate of [mySQL: insert values into 2 tables with foreign key relationship](https://stackoverflow.com/questions/51210533/mysql-insert-values-into-2-tables-with-foreign-key-relationship) — Jens, Jul 09 '18 at 08:15
No, it is not a duplicate because this one is about UPDATING existing values. The old post was about INSERTING new values into form fields and add those entries to a mysql database. — timunix, Jul 09 '18 at 15:20

score 0 · Accepted Answer · answered Jul 09 '18 at 10:10

With update statements, the auto_increment value doesn't change. And, as I can see from your query, you're not updating the maID value, so it gives no reason for the MySQL parser to throw an error. Your query is correct, as far as I can see.

Just one small thing. Define the keys of the associative array without the : symbol. You use this symbol to indicate that this place is reserved for the value stored in the variable by the following name. For example, using

$stmt = DatabaseLink::getInstance()->prepare("update table_name set name=:name where id=:id");

$status = $stmt->execute(array("name" => "test", "id" => 2));

indicates to the parser that the name corresponding to ID 2 has to be updated to test.

But, you are already using the : along with the name of the key. So, in your example, your query looks for the value in a key called maAnrede in your script, but the key that you have defined is :maAnrede, and hence, the query doesn't work as expected.

Try this change. It'll surely work.

DatabaseLink::getInstance()->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if(isset($_POST["btnSaveMfk"]))
{
$stmt = DatabaseLink::getInstance()->prepare("UPDATE mitarbeiter SET 
maAnrede = :maAnrede, 
maVname = :maVname, 
maName = :maName, 
maMobilfunkNr = :maMobilfunkNr,
maKartenanzahl = :maKartenanzahl,
maEmail = :maEmail,
maFirma = :maFirma
WHERE mitarbeiter.maID = :maid;
UPDATE mobilfunkkarten SET mfkTarif = :mfkTarif,
mfkStatus = :mfkStatus, 
mfkPin = :mfkPin,
mfkSuperpin = :mfkSuperpin");

$status = $stmt->execute(array(

"maid" => $_POST["txtMaId"],
"maAnrede" => $_POST["txtAnrede"],
..................,
..................,
..................
)); 
header("Location: Mobilfunkdaten"); //back to the PHP table that shows all entries
}

This situation happened with me as well, and this is the solution that worked for me!

Thank you for trying to help. Apparently, my statement is not correct because when I click on "save", only the ma-values from the table "mitarbeiter" will be updated correctly. However, edited mfk-Values from the table "mobilfunkkarten" will be applied to all rows, not only to one (the one I am editing), as it should be. For example, if i edit mfkTarif by typing into the form field "LTE 3 GB", this value will not only be applied to one ID but to all IDs in the table "mobilfunkkarten". How can I fix this? — timunix, Jul 09 '18 at 15:17
That's because when you use the `;` in the query statement, it terminates the current one, and treats the following query statement as an independent one. You need to use the `where` clause in the second query as well to update specific rows. Otherwise, it'll update all rows with the new values. — Tushar Udayan Tiwari, Jul 10 '18 at 07:41
@timunix you added another answer where you've asked about a better approach for it. From what I understand by looking at your table structure, one worker can have multiple SIM cards. So, if you update the tuples in the respective tables using the query mentioned, it'll update the details of all the SIM cards owned by a specific worker. Instead, use the `mitarbeiter.maID` and the `mobilfunkkarten.mfkID` to update only the required record in the dependent table (mobilfunkkarten). — Tushar Udayan Tiwari, Jul 10 '18 at 07:52

score 0 · Answer 2 · answered Jul 09 '18 at 15:35

I believe I fixed it. You need to add this line in the second SQL statement:

WHERE mobilfunkkarten.maID = :maid");

See below where I included it. Fixed the issue for me but I am not entirely sure how safe this one is...any criticism on this approach? Other suggestions?

DatabaseLink::getInstance()->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if(isset($_POST["btnSaveMfk"]))
    {
    $stmt = DatabaseLink::getInstance()->prepare("UPDATE mitarbeiter SET 
    maAnrede = :maAnrede, 
    maVname = :maVname, 
    maName = :maName, 
    maMobilfunkNr = :maMobilfunkNr,
    maKartenanzahl = :maKartenanzahl,
    maEmail = :maEmail,
    maFirma = :maFirma
    WHERE mitarbeiter.maID = :maid;
    UPDATE mobilfunkkarten SET mfkTarif = :mfkTarif,
    mfkStatus = :mfkStatus, 
    mfkPin = :mfkPin,
    mfkSuperpin = :mfkSuperpin
    WHERE mobilfunkkarten.maID = :maid");

    $status = $stmt->execute(array(

    "maid" => $_POST["txtMaId"],
    "maAnrede" => $_POST["txtAnrede"],
    ..................,
    ..................,
    ..................
    )); 
    header("Location: Mobilfunkdaten"); //back to the PHP table that shows all entries
    }

mySQL: UPDATE statement for 2 tables with a foreign key relation

2 Answers2