Honestly, there must be an easy way to do this.

I have 2 tables.

Users

Groups

When a user registered they are added to the Users table. Their user id is automatically assigned.

I also need to add the user to the 'groups' table. The 'groups' table contains user_id and group_id.

Is there some SQL magic that allows me to insert a user into the users table, grab the user_id and insert the user into groups table?

without using 3 sql statements? I don't know ... don't think so

I have always used 3 SQL statements. Just seems like a long way to do a simple task.

I hear ya! Maybe Luke or Jan knows... or someone else

nope, you can only INSERT into one table at a time.... (as far as I know....)
I'd love to be proved wrong!!!

Actually, you can do it in two statements!

People normally use the mysql_insert_id function to obtain the automatically generated value from the first INSERT. But you can shortcut because mysql gives you the LAST_INSERT_ID() function that you can use directly in your SQL statements.

For example


The full documentation can be found at http://dev.mysql.com/doc/refman/6.0/en/inf..._last-insert-id

that's a nice little trick!! thanks for sharing

That sounds useful since it says the ID generated is maintained on a per connection basis, so you won't have problems on a busy server like you could with mysql_insert_id.

Dave

Slightly off-topic, sort of, but why does the Groups table have a "user_id"? Shouldn't the Users table have a "group_id", and thus belongs to that group?

Maybe I'd need to see the rest of the table structure to understand... but how are you making more than one user a member of the same group?

Going really offtopic here but I would use three tables. Users/groups/usergroups, the third table would be a joining table which would allow users to be part of multiple groups so it would have these two columns (userid, groupid).

LAST_INSERT_ID is a good choice anytime since otherwise you'd have to get the id based on the values and that doesn't sound so foolproof.

You cannot insert into two tables at once.

@Antti: I agree!

This would obviously be much easier if you're using some kind of framework, and an MVC approach, then you likely won't even have to worry about MySQL... but it might be a steep learning curve.

When inserting into numerous tables, you may want to check out Transactions (BEGIN, ROLLBACK, COMMIT) to ensure that all the data was inserted before commiting it.