MySQL Query for Inserting New User Into Database

[JasonDFR]

I have two tables.

One called USERS, one called USER_INFO

USERS has columns `user_id`, `email`, `pass_word`, `active`, `date_created`

USER_INFO has columns `user_id`, `first_name`, `last_name`, `department`, `gender`

My question: When a new user provides the required information, how do I write the SQL queries to ensure that the user_id for in both tables is the same, thus linking first_name, last_name, etc, TO email, pass_word, etc.

INSERT into `users` (`email`, `pass_word`, `active`) VALUES ('$email', '$first_name', etc) will put the information into the USERS table, but now I need to put the rest of the information into the USER_INFO table while ENSURING that the `user_id` is the same for both tables.

`user_id` in the USERS table is auto_increment and primary key.

I know a bit about this stuff, but for some reason I can't get my head around this issue.

Thanks a lot!

JasonDFR

[boogyman]

the two tables cannot both have an auto-incrementing field that is used for the same purpose. one of those tables, will need to have a foreign key that points to the primary key in the opposite.

To actually do the double insert, you would need to do the insert into the table with the auto-incrementing user_id field
By checking that INSERT for no errors, you will than be able to do a second query that grabs the last inserted id, then once you have the last inserted user_id, you may do a second insert query to put the second record in the second table

if(query(INSERT...))
{
   $user_id = fetch_array(query(SELECT user_id FROM ___ ORDER BY user_id DESC LIMIT 1));
   if($user_id>0)
   {
       query(INSERT....)
   }
}