moving records from one table to another

[cerr]

Hi,

I'm working on some user registration precedure. When users register,
the set gets stored in a temporary table and only when they click on
the link in the email they get sent, I want to move the record from
the temporary table to my registered user table and remove it from the
temporary one. Can I do this move directly with MySql or do I need to
select the data and insert them again?

Thanks for help and suggestions!
Ron

[cerr]

Post by cerr
Hi,
I'm working on some user registration precedure. When users register,
the set gets stored in a temporary table and only when they click on
the link in the email they get sent, I want to move the record from
the temporary table to my registered user table and remove it from the
temporary one. Can I do this move directly with MySql or do I need to
select the data and insert them again?
Thanks for help and suggestions!
Ron

SWEET, I found it out:
INSERT... SELECT... FROM... WHERE ...

[Gordon Burditt]

Post by cerr
I'm working on some user registration precedure. When users register,
the set gets stored in a temporary table and only when they click on
the link in the email they get sent,

I would recommend one table. When users register, the record has
status = 'PENDING' (status is likely of type enum). When users
click on the link, the status is changed to 'ACTIVE'. Most functions
require a user status of 'ACTIVE' to use them. If a user doesn't
pay or misbehaves, the status is set to 'SUSPENDED' or 'CANCELLED',
leaving existing posts or whatever intact. Never delete a record.

Among other benefits, you don't have to check a potentially duplicate
username against two tables to see if it is a duplicate during
registration.

Post by cerr
I want to move the record from
the temporary table to my registered user table and remove it from the
temporary one. Can I do this move directly with MySql or do I need to
select the data and insert them again?

INSERT INTO ... SELECT ...
will do this. It's a little more complicated if the two tables don't
have the same fields.

[1-script.com]

responding to
http://www.1-script.com/forums/mysql/moving-records-from-one-table-to-another-8989-.htm

Post by Gordon Burditt
I would recommend one table. When users register, the record has
status = 'PENDING' (status is likely of type enum). When users
click on the link, the status is changed to 'ACTIVE'.

It may actually still make sense to do it exactly as the OP had suggested:
create two identical tables but make one for unconfirmed registrations.
This may come very handy if the system does not have a tightly moderated
registration system and is totally dependent on new users self-confirming
themselves via email. There could be thousands of those bogus
registrations that never get confirmed, even with captchas and other
precautions. In most forums, for example, bogus registrations completely
drown out the good ones. I've seen ratios of 50,000:1

It may be easier from the administration stand point to have the
non-trusted ones in a separate table. For example, you can chose to not
back it up or do it less often than the one with actual good users. Since
the OP seems to be on the early development stage now, it should be very
easy to build the separation into the system and just call different
tables based on whether the feature is for confirmed users or not.