Jay Wit Jay Wit - 2 months ago 26
MySQL Question

MySQL Insert into multiple tables? (Database normalization?)

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 mutliple 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 user to the "manual" userid for the profile table?

Answer

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. Is 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 :)