MySQL Insert into multiple tables? (Database normalization?)

Question

I tried searching a way to insert information in multiple tables in the same query, but found out it's impossible? So I want to insert it by simply using multiple queries i.e;

INSERT INTO users (username, password) VALUES('test', 'test')
INSERT INTO profiles (userid, bio, homepage) VALUES('[id of the user here?]','Hello world!', 'http://www.stackoverflow.com')

But how can I give the auto-increment id from the users to the "manual" userid for the profile table?

Answer 1

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!', 'http://www.stackoverflow.com');
COMMIT;

Have a look at LAST_INSERT_ID() to reuse autoincrement values.

Edit: you said "After all this time trying to figure it out, it still doesn't work. Can't I simply put the just generated ID in a $var and put that $var in all the MySQL commands?"

Let me elaborate: there are 3 possible ways here:

1. In the code you see above. This does it all in MySQL, and the LAST_INSERT_ID() in the second statement will automatically be the value of the autoincrement-column that was inserted in the first statement.

   Unfortunately, when the second statement itself inserts rows in a table with an auto-increment column, the LAST_INSERT_ID() will be updated to that of table 2, and not table 1. If you still need that of table 1 afterwards, we will have to store it in a variable. This leads us to ways 2 and 3:

2. Will stock the LAST_INSERT_ID() in a MySQL variable:

   INSERT ...
   SELECT LAST_INSERT_ID() INTO @mysql_variable_here;
   INSERT INTO table2 (@mysql_variable_here, ...);
   INSERT INTO table3 (@mysql_variable_here, ...);
   

3. Will stock the LAST_INSERT_ID() in a php variable (or any language that can connect to a database, of your choice):

   • INSERT ...
   • Use your language to retrieve the LAST_INSERT_ID(), either by executing that literal statement in MySQL, or using for example php's mysql_insert_id() which does that for you
   • INSERT [use your php variable here]

WARNING

Whatever way of solving this you choose, you must decide what should happen should the execution be interrupted between queries (for example, your database-server crashes). If you can live with "some have finished, others not", don't read on.

If however you decide "either all queries finish, or none finish - I do not want rows in some tables but no matching rows in others, I always want my database tables to be consistent", you need to wrap all statements in a transaction. That's why I used the BEGIN and COMMIT here.

Comment again if you need more info :)