INSERT INTO a table from another table, but with the correct user ID for each comment a user adds.

Question

I have two tables - a 'users' and a 'comments'. They both have the coloum 'IDUsers'. When a user adds a comment I want their user ID to come from the 'users' table and go into the 'comments' table into the 'IDUsers' coloum from the 'IDUsers' coloum.

However, at the same time a comment is being added from the user - so I also am using the INSERT INTO for the new information. I'm also using ColdFusion - if that makes much difference.

Hope everyone understands what I'm saying....and thanks for the help.

Answer 1

What exactly is the problem? When a user is posting a comment, you know their user ID. So use that UserID to do the insert into the comments table.

If you're generating the user object on the fly - as a part of the same transaction (make sure it's a single transaction! It's important - synchronization issues!!!) - use something like mysql_insert_id() equivalent in ColdFusion (http://www.coldfusionmuse.com/index.cfm/2005/8/8/identity%20after%20insert). After you do the insert into the user table, get the ID of the inserted record; then use that ID as the foreign key into the comments record.

Answer 2

You will know the userId while a user is adding comments. Use that particular userId in the insert statement. And to make sure that only userId from users table comes in comments table, you can set userId in comments table as a foreign key from users table.