TECH6.0

webwizzy · #1 31-12-2008, 11:53 PM

Part I - User Registration System Setup
Part III - Forgot Password feature Implementation

Part II - Account Confirmation or Email Authentication

Alright, if you have followed the tutorial carefully uptill now, you should have successfully completed Part I.

So, confirmation email is dispatched. Now we need to code confirm.php which is pretty easy

As mentioned earlier, read the comments to understand code better. Though if you haven't changed any vars or anything else, this should work as it is. Simply upload this file.

confirm.php

PHP Code:

  <?php include('connection.php');?>
<?php
$colname_rsDisplay = "-1";
if (isset($_GET['passkey'])) {
  $colname_rsDisplay = $_GET['passkey'];
}
mysql_select_db($database_vin_conn, $vin_conn);

// Retrieve data from table where row that match this passkey
$sql1="SELECT * FROM temp_data WHERE confirm_code ='$colname_rsDisplay'";
$result1=mysql_query($sql1);

// If successfully queried
if($result1){

// Count how many row has this passkey
$count=mysql_num_rows($result1);

// if found this passkey in our database, retrieve data from table "temp_data"
if($count==1){

$rows=mysql_fetch_array($result1);
$password=$rows['password'];
$email=$rows['email'];
$firstname=$rows['firstname'];
$lastname=$rows['lastname'];
$sex=$rows['sex'];
$dateofbirth=$rows['dateofbirth'];
$monthofbirth=$rows['monthofbirth'];
$yearofbirth=$rows['yearofbirth'];
$city=$rows['city'];
$country=$rows['country'];
$ip=$rows['ip'];
$joindate=$rows['joindate'];

// Insert data that retrieves from "temp_data" into table "registered_users"
$sql2="INSERT INTO registered_users(password, email, firstname, lastname, sex, dateofbirth, monthofbirth, yearofbirth, city, country, ip, joindate)VALUES('$password', '$email', '$firstname', '$lastname', '$sex', '$dateofbirth', '$monthofbirth', '$yearofbirth', '$city', '$country', '$ip', '$joindate')";
$result2=mysql_query($sql2);
}
else{
// if not found passkey, display message "Wrong Confirmation code"
header('Location: activation_fail.php');  
exit;
}
// if successfully moved data from table "temp_data" to table "registered_users" displays message "Your account has been activated" and don't forget to delete confirmation code from table "temp_data"
if($result2){

header('Location: activation_thanks.php');

// Delete information of this user from table "temp_data" that has this passkey
$sql3="DELETE FROM temp_data WHERE confirm_code = '$colname_rsDisplay'";
$result3=mysql_query($sql3);

}

}
// Written by webwizzy. Copyright tech6.com
?>

The user clicks on the activation link in the confirmation email and with the help of the unique passkey in the URL, his registered info is inserted into registered_users table and deleted from temp_data

So, account confirmed/activated and user registration is complete.

We will deal with Forgot Password feature next.

TaL · #2 02-01-2009, 11:46 PM

Hi Webwizzy

Thanks for getting this out. It is a great project that has taught me a lot. I have a couple of points of note just to help make this as complete as possible and a question though....

-The first thing that I noticed was that in confirm.php the code "include 'vin_conn.php'" should probably read "include 'connection.php'" as connection.php is used in part 1.
-Just to keep the naming convention consistent, activationthanks.php should be activation_thanks.php
The fact that I have no other comments is a testimonial to your skill as a programmer.

I find that it works perfectly now with only one problem...The passkey is handled properly, the data is retrieved from temp_data properly, the data is inserted into registered_users properly, the data is deleted from temp_data properly, and then the confusing part...I am directed to the activation_fail.php page.

I am not a programming whiz and I am baffled by the action of the code...I don't understand how it can all be running correctly (especially the delete) but still execute the ELSE that redirects me to the activation_fail.php. I could understand it if the code was not executing properly but with it working perfectly, I see no reason how I could be directed to the activation_fail.php and not the activation_thanks.php page.

The only changes I have made to the provided script are that I include "connections.php" not "vin_conn.php", I have the line to use the inbuilt sha256 function to encrypt the password in require.php, and I have added a username field as part of the data from temp_data.
Other than that, the script should be exactly the same. I expanded some commenting just so I could understand better. Here is my confirm.php I hope you can explain this to me.

confirm.php:

PHP Code:

  <?php include('files/phpSnips/connection.php');?>
<?php
$colname_rsDisplay = "-1"; //initialize the variable
if (isset($_GET['passkey'])) { //if the passkey is provided to the script
    $colname_rsDisplay = $_GET['passkey']; //set the passkey to the search paramiter
}
mysql_select_db($database_vin_conn, $vin_conn); //connect to the database
// Retrieve data from table where row that match this passkey
$sql1="SELECT * FROM temp_data WHERE confirm_code ='$colname_rsDisplay'"; //define the sql query with the search paramiter
$result1=mysql_query($sql1); //store the result of the query
// If successfully queried
if($result1){ //boolean check if result1 is true then it retreved at least 1 row with a mathcing passkey therefore do the following
    // Count how many rows have this passkey
    $count=mysql_num_rows($result1);
    if($count==1){ // if only 1 instance of this passkey was found in our database, retrieve the data from table "temp_data"
        $rows=mysql_fetch_array($result1);
        $password=$rows['password'];
        $email=$rows['email'];
        $firstname=$rows['firstname'];
        $lastname=$rows['lastname'];
        $username=$rows['username'];
        $sex=$rows['sex'];
        $dateofbirth=$rows['dateofbirth'];
        $monthofbirth=$rows['monthofbirth'];
        $yearofbirth=$rows['yearofbirth'];
        $city=$rows['city'];
        $country=$rows['country'];
        $ip=$rows['ip'];
        $joindate=$rows['joindate']; //set the variables for insertion to registered_users
        $sql2="INSERT INTO registered_users(password, email, firstname, lastname, username, sex, dateofbirth, monthofbirth, yearofbirth, city, country, ip, joindate)VALUES('$password', '$email', '$firstname', '$lastname', '$username', '$sex', '$dateofbirth', '$monthofbirth', '$yearofbirth', '$city', '$country', '$ip', '$joindate')"; //define the insertion string
        // Insert data that was retrieved from table "temp_data" into table "registered_users"
        $result2=mysql_query($sql2); //run the insertion
    }
    else{ //to be here, the script did not find only 1 instance of the passkey. Display message "Wrong Confirmation code"
        header('Location: activation_fail.php');
        exit;
    }
    // if successfully moved data from table "temp_data" to table "registered_users" displays message "Your account has been activated" and don't forget to delete confirmation code from table "temp_data"
    if($result2){ //if the insertion was successfull
        // Delete information of this user from table "temp_data" that has this passkey
        $sql3="DELETE FROM temp_data WHERE confirm_code = '$colname_rsDisplay'";
        $result3=mysql_query($sql3);
        //goto the activation_thanks
        header('Location: activation_thanks.php');
        exit;
    }
}
// Written by webwizzy. Copyright tech6.com
?>

Thanks again for the great tutorial.

webwizzy · #3 03-01-2009, 12:11 AM

First of all, thanks for pointing out those two things. I have corrected them in my previous post. I really like it when you pointed out these mistakes, this assures that you really looked into it deeply.

I am not a programming whiz

Even i am not

I see no reason how I could be directed to the activation_fail.php and not the activation_thanks.php page.

Apart from the changes you mentioned, you have even added an exit; in if($result2) condition.

Try replacing your last IF condition with this:

PHP Code:

  if($result2)

{

     header('Location: activation_thanks.php');

 
    // Delete information of this user from table "temp_data" that has this passkey

    $sql3="DELETE FROM temp_data WHERE confirm_code = '$colname_rsDisplay'";

    $result3=mysql_query($sql3);

}

TaL · #4 03-01-2009, 12:29 AM

Thanks for the quick response

You are very correct about the extra exit command. I put it there trying to figure out what was wrong and forgot to remove it. I have replaced the last conditional check with the code you posted and I am baffled to say that I have the same results. I am still directed to the activation_fail page but the code executes correctly. I have my data in the registered_users table and my data has been removed from the temp_data table.

I am stumped.

I doubt it makes any difference but I am running this on Hardy Kubuntu Linux with Apache web server and MySQL.

webwizzy · #5 03-01-2009, 12:48 AM

Now thats really strange. But IT HAS TO BE some small mistake somewhere. You need to look into it carefully. There's no way if and else both can execute. Try emptying both the tables in your test database and test out fresh.

Are you sure you are being directed to activation_fail.php? lol it has happened with me once that by mistake I wrote "Activation Failure" in thanks page ha ha.

Also, did you try registering at my DEMO site, it runs the same exact code in confirm.php. It does work good.

nah.. i don't think your system can be a problem.

TaL · #6 03-01-2009, 02:03 AM

O.K.

Here we go again. I have checked everything I can think of (so I obviously can't think of it right now) and attached a screen shot of what is happening.

In this screen shot you can see I started with 2 empty tables, the activation e-mail in the upper right, that the actual activation_fail.php file is displayed and that the data ends up in the registered_users table. I agree with you that I must have something small and stupid wrong but I just can't seem to track it down. I ran through your demo registration and it worked exactly as I thought it should.

I just hope you have another idea for me...otherwise I will take a break from it for a couple of days then come back with fresh eyes

webwizzy · #7 03-01-2009, 07:13 AM

At this point, all I can say is to tally YOUR confirm.php (the one you had uploaded) with mine (line by line)

It has to work.

Also try echo'ing statements rather than directing to fail/thanks page.

PHP Code:

  echo "Thank you for activating your account";

btw, your screenshot is just too small.

TaL · #8 03-01-2009, 09:21 PM

Hi again

OK. I replaced the header calls with echo statements and all works perfectly. Data gets handled properly and I get the thanks message. Everything works exactly as expected.

Here is the code from the else down to the end with the working echo statements:

PHP Code:

  else{ //to be here, the script did not find only 1 instance of the passkey. Display message "Wrong Confirmation code"
//        header('Location: activation_fail.php');
        echo "The activation failed";
        exit;
    }
    // if successfully moved data from table "temp_data" to table "registered_users" displays message "Your account has been activated" and don't forget to delete confirmation code from table "temp_data"
    if($result2){
//        header('Location: activation_thanks.php');
        echo "Thank you for activating your account"; 

        // Delete information of this user from table "temp_data" that has this passkey
        $sql3="DELETE FROM temp_data WHERE confirm_code = '$colname_rsDisplay'";
        $result3=mysql_query($sql3);
    }
}
?>

and this works great. It displays the message "Thank you for activating your account" and moves the data properly.

If I take the echo out and put back in the header('Location: activation_thanks.php') call, then I get "the activation failed" message.

Here is the code from the else down with the header calls in:

PHP Code:

  else{ //to be here, the script did not find only 1 instance of the passkey. Display message "Wrong Confirmation code"
//        header('Location: activation_fail.php');
        echo "The activation failed";
        exit;
    }
    // if successfully moved data from table "temp_data" to table "registered_users" displays message "Your account has been activated" and don't forget to delete confirmation code from table "temp_data"
    if($result2){
        header('Location: activation_thanks.php');
//        echo "Thank you for activating your account"; 

        // Delete information of this user from table "temp_data" that has this passkey
        $sql3="DELETE FROM temp_data WHERE confirm_code = '$colname_rsDisplay'";
        $result3=mysql_query($sql3);
    }
}
?>

and this directs me to the activation_fail.php and it handles the data properly. Any other ideas?

webwizzy · #9 03-01-2009, 10:26 PM

okay.. so echo works perfect. header() is creating some problem.

Which browser you're using and what is your PHP version.

And I'll just like to confirm if you do have "Thank you" in activation_thanks.php and not the opposite.

TaL · #10 04-01-2009, 12:15 AM

OK...so everything is working perfectly and I found the problem...the problem had nothing to do with the code at all LOL. When you said it had to be something simple and stupid, you were right

The problem was that:
Normally I use G-mail for my e-mail but for my site, I have setup a local Postfix server and I am using Kontact with K-mail as my local client. I never bothered to configure K-mail and it was launching the link in Konqueror and not in Firefox! I changed the setup for K-mail and it is working perfectly now

Thanks for the help tracking it down...your last post really got my mind working properly LOL

webwizzy · #11 04-01-2009, 11:24 AM

ha ha glad you got it working tal.. cheers

TaL · #12 13-02-2009, 01:08 AM

Hello again

I showed my new user registration system to a friend and he advised me to make 1 change...
In the confirm.php file, when we are taking the data out of temp_data and putting it into registered_users, we should be using SQL transaction to make sure that if anything goes wrong, the databases will not become corrupted. I realize that this is a beginner tutorial so you may not have wanted to include it but I thought people may like to see an example of it so here is my section of the confirm.php file that deals with that:

PHP Code:

  if($result1){ //boolean check if result1 is true then it retreved at least 1 row with a mathcing passkey therefore do the following
    // Count how many rows have this passkey
    $count=mysql_num_rows($result1);
    if($count==1)
        { // if only 1 instance of this passkey was found in our database, retrieve the data from table "temp_data"
        $rows=mysql_fetch_array($result1);
        $imageDatabase=$rows['sec_id'];
        $password=$rows['password'];
        $email=$rows['email'];
        $firstname=$rows['firstname'];
        $lastname=$rows['lastname'];
        $username=$rows['username'];
        $sex=$rows['sex'];
        $dateofbirth=$rows['dateofbirth'];
        $monthofbirth=$rows['monthofbirth'];
        $yearofbirth=$rows['yearofbirth'];
        $city=$rows['city'];
        $country=$rows['country'];
        $ip=$rows['ip'];
        $joindate=$rows['joindate']; //set the variables for insertion to registered_users

        mysql_query("start transaction");

        $sql2="INSERT INTO registered_users(password, email, firstname, lastname, username, sex, dateofbirth, monthofbirth, yearofbirth, city, country, ip, joindate, imageDatabase)VALUES('$password', '$email', '$firstname', '$lastname', '$username', '$sex', '$dateofbirth', '$monthofbirth', '$yearofbirth', '$city', '$country', '$ip', '$joindate', '$imageDatabase')"; //define the insertion string
        // Insert data that was retrieved from table "temp_data" into table "registered_users"
        $result2=mysql_query($sql2); //run the insertion

        // Delete information of this user from table "temp_data" that has this passkey
        $sql3="DELETE FROM temp_data WHERE confirm_code = '$colname_rsDisplay'";
        $result3=mysql_query($sql3);
    
        if ($result2 and $result3)
        {
            mysql_query("commit");
        }
        else
        {
            mysql_query("rollback");
        }

    }
    else{

We still leave the redirection in the same place but we move both database queries into the same loop and surround them with transaction database protection

webwizzy · #13 13-02-2009, 10:30 AM

So it rollbacks if some error occurs in $result2 and $result3, is that so?

May I know what is the significance of this line:

PHP Code:

  mysql_query("start transaction");

And btw TaL, I must tell you that if you're actually following this tutorial on a live website, then this is not the best and latest way of doing this, read my post #20 onwards which explains the latest trend. I wonder how you missed it.

Use of just a single update query to confirm users is far better and practical rather than two heavy insert and delete queries. Infact almost every web-script does it the same way. You won't find it tough, its quite easy. I'd highly recommend to change it now, or else it may become troublesome to do it later.

TaL · #14 13-02-2009, 04:36 PM

That is exactly right. If we have an error in result 2 or result 3 then we rollback to the start of the SQL transaction which is denoted by

PHP Code:
mysql_query("start transaction");

By encapsulating the 2 database interactions inside the "transaction" then we make sure that if anything goes wrong with the database server during working with the 2 results, SQL will rollback to the start of the transaction to avoid database corruption.

I saw your post a while ago but I think I was still too new to the programming side of things to really understand what it meant so I just kept following the tutorial

. I do now though and I start to understand better. My friend did happen to mention that production websites do things differently but I think he was just happy to see me programming so he may not have wanted to say anything too critical so as not to make me feel too bad about what I have LOL.

As it stands now, I am very disappointed. I broke my login system and am having a hard time figuring out what I did to it. I will look at restructuring the way this registration system works when I fix my login system problems

Let this be a good reminder to all...WHEN YOU GET YOUR STUFF WORKING RIGHT...BACKUP BEFORE YOU START PLAYING WITH IT AGAIN!!!!!!!!! I am amazed that I did not backup my working stuff...thats what I get for working too late into the night and not thinking clearly. I think I am going to write a site backup script LOL.

webwizzy · #15 13-02-2009, 05:21 PM

Thanks for the explaination TaL.

haha.. yeah I remember you once told something about backups

And yes, once you fix your problem, go for the new procedure i.e. completely discard the use of temp_data table. There's no meaning left on following the old procedure, this one is far more efficient, fast and optimized.

Happy coding.

TaL · #16 14-02-2009, 10:54 AM

Well, I got it working again

Turned out to just be a stupid typo...1 extra letter in a variable name LOL. Soon I will be changing it to remove the temp_data table.
Also, not only do I now have 2 backups of my current working stuff, I have implemented a revision control system that has file change tracking

I will hopefully be safe from more late night coding problems hehe

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

TECH6.0

PHP User Registration System - Email Authentication