13

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

I would like to insert data from a php form into 3 different tables, which all have foreign keys. How can I write an insert command which updates all 3 tables at once, because if I try to update a table manually then I get an error because of the missing references. Do I have to deal with "NULL" entries and update every table after another or is it possible to solve this with one single command? Like MySQLi_Multi_Query?

Thank you very much!

Crayl
  • 1,883
  • 7
  • 27
  • 43

4 Answers4

32

You can do it in 3 Methods:

First & Recommended. USING SELECT IN THE INSERT VALUE: 

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

Second. USING LAST_INSERT_ID IN THE INSERT VALUE:

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

Third. USING PHP SCRIPT

<?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 );
?>
Mohamed Aladdin
  • 19
  • 9
Meeshal
  • 397
  • 1
  • 6
  • 10
  • 4
    Careful with the first and recommended suggestion. That assumes there is only one "John Smith". If name is not a UNIQUE column, that is asking for trouble. Using LAST_INSERT_ID() is the way to go. – kontiki Aug 23 '18 at 16:54
  • Are these multi-table inserts atomic? i.e. Either 2 tables are updated or zero (if there was any intermediate errors)? Or does one have to use `begin transaction` to ensure atomicity? – colm.anseo Feb 17 '20 at 14:16
10

You're most likely going to have to insert things in order of their dependence. So if you have three tables (A, B, and C) we'll assume C depends on B and B depends on A. We'll also assume each table has primary keys AID, BID, and CID respectively.

  1. You'd insert your row into A and get AID.
  2. Then you'd insert your row into B using the AID you got from step 1.
  3. Then you'd insert your row into C using the BID (and perhaps AID) you got from step 2 (and perhaps 1)
Corith Malin
  • 1,505
  • 10
  • 18
  • In your way, we have to execute sql one by one, this may cause perfmance problems. Is there a solution that can insert all these related rows in one sql? Now, I'm thinking, if I can get a list of AID without insert rows right now? – oppo Jun 21 '17 at 09:50
  • You could look into bulk inserts with MySQL (I only have experience with it in MSSQL). Then you could wrap all three bulk inserts into a transaction (so if one fails, all are rolled back) – Corith Malin Jun 22 '17 at 18:27
4

There is an error in your syntax for the method 1. 

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

should be

INSERT INTO user_details (id, weight, height)
     VALUES ((SELECT id FROM user WHERE name='John Smith'), 83, 185);
Holger Will
  • 7,228
  • 1
  • 31
  • 39
ChinChiller
  • 41
  • 1
1

1) The sense of foreign_key is to associate a value in a field with a pre_existing value somewhere else. So you should make your inserts in a logic order.

2) If you want to avoid logical restrictions you should 

SET foreign_key_checks = 0  // disable key checks in server
INSERT ... // any order
INSERT ...
...
SET foreign_key_checks = 1
bortunac
  • 4,642
  • 1
  • 32
  • 21