[RESOLVED] search into 2 tables

vang

hi,
i have 2 tables:

table 1: register_email, name, ....
table 2: confirm_email, name, ....

When a new user register, i want to check in table 1 & table 2 to make sure there are no duplicate email in both tables before i insert the new data.
I've achieved this with 2 sql statements, but i think this can be done in 1 sql statement. Can sql expert show me how this can be done, thanks.

djjjozsi

store the register email and confirm_email in one table.

and give us better explanation what is your goal with this two table.
name is a field you need to normalize. new table for names, and use its primary key in emails table as foreign key. This method garanttee that there won't be duplicate your names. My name is jjozsi, if i register into your site you need to allow another jjozsi to enter his name. Is it a good idea to set this field as unique?

vang

hi,
i use 2 tables for register and account activation.
new user register and store data on table 1.
user activate the account through email will move the data from table 1 to table 2.
just in case some user registered but haven't activate their account and a new user register, i need to check table 1 and table 2 for duplicate email.

so how can 1 do it in a single sql statement, thanks:
$new_register_email = $_POST['email'];
table 1: registered_email,
table 2: confirmed_email,

djjjozsi

you should make one table:

emails_table:
name, pass,email,activated,date_of_registration

if the user activated his email, lets set a flag to 1, if the user activated his email with the link. On a login procedure select the email from the table where activated is 1.

Use a date_of_registration field to track when did the user register his account. And delete from the table if the date_of_registration is bigger then 5 days (from now() ) without activation.

two tables make this project difficult to handle.

If you keep this design i suggest hard code the email check with php.

vang

hi,
i have other tables also for storing the user session, user login attempt, user banned list, user permission etc. If i were to squeeze everything in 1 table, then it is very messy.

djjjozsi

ideas may come from others, listen to them. I think i have told you a better table design to handle user activations.