insert in a related table mysql

Question

I have two MySQL tables (InnoDB). I have created a users table with "id_users" as the relationship key. Then I created a second table with a foreign key reference to "id_users" in the users table. How can I do a MySQL Insert into the second table?

Should I use JOIN?

`JOIN` is for `SELECT` statements only. First insert into one table, then into another — u_mulder, Nov 30 '14 at 18:52
@u_mulder: Not necessarily! I've used JOIN in DELETE and UPDATE statements a numerous times. Example: http://stackoverflow.com/questions/15209414/mysql-update-join — J A, Nov 30 '14 at 19:02
This link can help you: http://stackoverflow.com/questions/12073513/insert-using-left-join-and-inner-join — Geovani Santos, Nov 30 '14 at 19:18

score 1 · Answer 1 · answered Nov 30 '14 at 19:27

Join is only for SELECT statement. What you want to do is just two simple inserts :

Insert your row in users
Get the last id from users
Insert your row in the second table with the foreign key equal to the last id.

In mysql, this can be :

INSERT INTO user (id, username) VALUES (NULL, 'john');
INSERT INTO group (id, id_users) VALUES (NULL, LAST_INSERT_ID())

LAST_INSERT_ID works for the entire database... be carefull using it.

score 0 · Answer 2 · answered Nov 30 '14 at 19:36

0

Merlin's answer is almost correct.

You can go with PHP's mysql_insert_id() function to get last insert id.

If you are a novice programmer, I will prefer you to go and get you hands dirty on mysqli and pdo as mysql related functions are deprecated.

answered Nov 30 '14 at 19:36

Ravi

2,078
13
23

insert in a related table mysql

2 Answers2