更新表列同时作为查询？

Question

i have id set as an auto increment column in my table and i am trying to copy the data that is in column id over to column user_id.

the query below inserts data and auto increments the column id, but then i want the column user_id to have the same value as id, so if id is auto increment to 31 then user_id will also be 31.

here's the query I'm using:

$query="INSERT INTO ptb_users (user_id,
id,
first_name,
last_name,
email )
VALUES('NULL',
'NULL',
'".$firstname."',
'".$lastname."',
'".$email."'
)";
$result = mysql_query("UPDATE ptb_users SET user_id=id");
mysql_query($query) or die();

the problem i have is the update/copy between id and user_id will only happen for this current registration after another user registers / after the query is ran a second time.

so say id 31 needs to be copied to the column user_id this will not happen until another user signs up / runs the query the second time. if the new person registering is id 32 then the update between id and user_id of the the user before (id 31) will not take place till id 32 registers/runs the query a second time.

how can i get the update to take place with the query or as soon as the query is finished so that when id 31 registers / runs the query the update between id and user_id happens at the same time as the query is run?

really i would prefer to have id and user_id as auto increment but because its not possible to have two auto increment fields i am trying to duplicate the auto increment result from id to user_id and I need to do it this way because my code on my site cross references the two columns for security.

Answer 1

Your update query runs at first, replace the call orders.

...    
mysql_query($query) or die();
$result = mysql_query("UPDATE ptb_users SET user_id=id");
...

I don't know why are you using such actions in Your code, there are many better ways to same thing. But please, add where condition, in your update statement because your query updates all table and it will work slow, and it is not optimal solution.