Insert into multiple MySQL tables with one-to-one relationship

I have been asked if there is a way to insert data into two MyISAM tables that connected by a one-to-one relationship in one MySQL query.

For example:

mysql> CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
 
mysql> CREATE TABLE `user_details` (
  `id` int(10) unsigned NOT NULL,
  `weight` int(10) unsigned default NULL,
  `height` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

How can I insert at once a new product with its details?
My answer is that you can’t insert into multiple MySQL tables in one query (if you know a way, I will be happy to learn;).
However, there are several simple and easy ways to do it:

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

Using LAST_INSERT_ID in the insert value:

INSERT INTO user (name)
     VALUES ('John Smith');
INSERT INTO user_details (id, weight, height)
     VALUES (LAST_INSERT_ID(),83, 185);

Note: The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

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

If you know of another good way please post a comment.

Note: This post was fixed according to the comments I have received from Singer Wang, Shlomi Noach and Chris Evers. Thanks guys!

MySQL Quiz
8 Comments
  1. Pascal says:

    I think, a good way will be to use stored procedure rather than separate queries :)

  2. Singer Wang says:

    Your argument doesn’t make sense. MyISAM and Foreign Keys?

  3. Anonymous says:

    In your example for the CREATE TABLE statement there was no FOREIGN KEY constraints made.

  4. Sorry, I have to throw in this little note: best not to discuss foreign keys while MyISAM examples.

    With your MyISAM tables, it is perfectly valid to first insert rows to `user_details`, then to `user`; whereas with foreign keys set in place that would be impossible.

    • Chris Evers says:

      I totally agree with Shlomi. Even more, I’d even that outside a transaction (not available with MyISAM, again), it could turn extremely dangerous for the consistency of data.

      The minimum would be to test the first result (in PHP), even if rollback isn’t available…

  5. Chris Evers says:

    … Even more, I’d add* that… (sorry)

  6. Ilan Hazan says:

    Thanks a lot friends.
    I have fixed my post.

  7. Octopus says:

    The Main Problem
    =================
    What if your first query executed successfully and the next one didn’t. Then a total chaos will happen. Products got inserted, but product customer is missing.

    There is no roll back for myisam tables. You will have to create your own roll backs.

Leave a Reply

*