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?

Answer 1

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

1. Insert your row in users
2. Get the last id from users
3. 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.

Answer 2

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.