PHP / SQL将用户标识添加到新用户的查找表中

Question

I have a script which adds a new user to the database and stores them in the users table. I also have a contracts table (a list of contracts) and a look-up/linking table for users to contracts.

I want to know the best way to add a new user to the contracts table when you will not know what the unique ID they will be given is?

Example:

step 1 - Sign-up form has normal user inputs, username, password ect and a number of checkboxes for contracts (all contracts have a unique ID also).

step 2 - the form is submitted and users details are added to the user table and a unique ID is generated for that new user.

step 3 - the contracts selected in step 1 are inserted into the users_contracts table using the unique ID of the contracts selected and the unique ID of the new user.

This is where im having a brain block. How do I know what the new users unique ID will be?

The only way I can think at the moment is to INSERT the new user as normal, then do another query for the new username and pull the new users ID from the database then INSERT it in to the users_contracts table in a third query.

Something like:

INSERT INTO `users` _POST DATA FROM FORM

Then get the above INSERTED user and store in a variable:

SELECT FROM users _POST USERNAME

Then add it to the users_contracts table:

INSERT INTO users_contracts _POST contracts and the above created variable for the users ID

SORRY should mention im using PDO for the insert and all database activity.

Answer 1

After running your user insert query, run:

$userId = mysql_insert_id();

Returns the ID of the previously inserted row.

EDIT: Since someone will mention it, you should actually use mysqli_insert_id() because mysql_insert_id() and other MySQL (as opposed to MySQLi) will be deprecated eventually.