MySQL - how to insert into multiple tables with foreign keys

Question

I am new to MySQL, so please be nice :)

I would like to insert data from a php form into 3 different tables, all of which have foreign keys. How to write an insert command that updates all 3 tables at once, because if I try to update the table manually, I get an error message due to missing links. Do I have to deal with "NULL" records and update each table after another, or can I solve this problem with one command? How is MySQLi_Multi_Query?

Many thanks!

Answer 1

, . , (A, B C), , C B B A. , AID, BID CID .

• AID.
• B AID, 1.
• C BID (, , AID), 2 (, , 1)

Answer 2

3- :

. :

   INSERT INTO user (name)
     VALUES ('John Smith');
INSERT INTO user_details (id, weight, height)
     VALUES ((SELECT id FROM user WHERE name='John Smith'), 83, 185);

-. LAST_INSERT_ID :

INSERT INTO a (id)
     VALUES ('anything');
INSERT INTO user_details (id, weight, height)
     VALUES (LAST_INSERT_ID(),83, 185);

-. PHP

<?php
// Connecting to database
$link = mysql_connect($wgScriptsDBServerIP, $wgScriptsDBServerUsername, $wgScriptsDBServerPassword, true);
if(!$link || !@mysql_SELECT_db($wgScriptsDBName, $link)) {
echo("Cant connect to server");
    exit;
}

// Values to insert
$name = 'John Smith';
$weight = 83;
$height = 185;

// insertion to user table
$sql = "INSERT INTO user (name) VALUES ('$name')";
$result = mysql_query( $sql,$conn );
// retrieve last id
$user_id = mysql_insert_id( $conn );
mysql_free_result( $result );

// insertion to user_details table
$sql = "INSERT INTO user_details (id, weight, height) VALUES ($user_id, $weight, $height)";
$result = mysql_query( $sql,$conn );
mysql_free_result( $result );
?>

Answer 3

There is an error in your method 1 syntax.

INSERT INTO user_details (id, weight, height)
     VALUES (SELECT(id FROM user WHERE name='John Smith'), 83, 185);

it should be

INSERT INTO user_details (id, weight, height)
     VALUES ((SELECT id FROM user WHERE name='John Smith'), 83, 185);

Answer 4

1) The feeling of foreign_key is to associate a value in a field with a preliminary value elsewhere. Therefore, you must make your insertions in a logical order.

2) If you want to avoid logical restrictions, you must

SET foreign_key_checks = 0  // disable key checks in server
INSERT ... // any order
INSERT ...
...
SET foreign_key_checks = 1