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!
A table can have multiple foreign keys based on the requirement.
No, you can't insert into multiple tables in one MySQL command. You can however use transactions. BEGIN; INSERT INTO users (username, password) VALUES('test', 'test'); INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello world!
A foreign key constraint always references one target table. Polymorphic Associations are supported by frameworks such as Rails and Hibernate. But they explicitly say that you must disable SQL constraints to use this feature.
If you are inserting data into a dependent table with foreign keys: Each non-null value you insert into a foreign key column must be equal to some value in the corresponding parent key of the parent table. If any column in the foreign key is null, the entire foreign key is considered null.
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 );
?>
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With