Verify Email and Update MySQL DB Column

Question

Need help in creating a verify.php / confirm.php for my site after registration. Email is already being sent to the email of user with a confirmation link. I need the link to work like this:

When link is clicked, user will be directed to our site and will echo a simple message and update a certain column in our database that would state the user is VERIFIED from being UNVERIFIED user.

Thanks in advance!

Answer 1

I would go about it like this:

1. Have a users table with atleast the following columns: username (varchar), vrified (bool) and token(varchar).
2. When a user registers the verified column would be 0 (UNVERIFIED) and generate a random token and insert that with all the other info you solicit. I won't go on how to do that as their are lots of tutorials on the internet on how to generate a random token.
3. After you're all done inserting the new user into the DB, you would need to send the email with the confimation link, this link could have the email and the token of the user. Example: www.mysite.com/verify.php?email=USERS_EMAIL&token=GENERATED_TOKEN.
4. After the email has been sent, on verify.php you would need to check if the user exists in the DB and update that user.

Example Queries:

<?php
    $checkUserExists = "SELECT COUNT(username) FROM users WHERE email = USERS_EMAIL"; //This should return a 1 if the user exists.

    $updateUser = "UPDATE users SET verified = 1 WHERE email = USERS_EMAIL AND token = GENERATED_TOKEN"; //Update the user if he exists.
?>

5. If the user doesn't exist or token isn't correct, you can send an error message. Else show that the user has updated correctly.

Hope this kinda helped you out on how to approach this. Please write your own code or post up what you have tried, as it was said in the comments, we are more than willing to help out but we won't do all the work you have to do.