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')
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.
Let me elaborate: there are 3 possible ways here:
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
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:
Will stock the
a MySQL variable:
INSERT ... SELECT LAST_INSERT_ID() INTO @mysql_variable_here; INSERT INTO table2 (@mysql_variable_here, ...); INSERT INTO table3 (@mysql_variable_here, ...);
Will stock the
LAST_INSERT_ID() in a
php variable (or any language that
can connect to a database, of your
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]
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
Comment again if you need more info :)