faqts : Computers : Programming : Languages : PHP : Database Backed Sites : MySQL

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

4 of 4 people (100%) answered Yes
Recently 4 of 4 people (100%) answered Yes


How can I activate new sign ups sending them an email with a unique URL that expires in 72 hrs?

Jun 26th, 2004 22:09
Philip Olson, Richard Jones,

There are a few ways to do this, here's one:
a) Sign-up form
b) Upon submit insert data into a database with three additional 
fields labeled:
   - uniqueid  (32 character CHAR)
   - active    (1 for active, 0 for inactive)
   - date      (a unix timestamp)
The 32 character uniqueid can be generated with the following:
   $uniqueid = md5(uniqid(rand(), true));
c) Upon submission all fields are entered (active = 0) and an email is 
sent to them with a link to the confirmation page.  This confirmation 
page will check the unique id that's included in the generated URL:
So for example:
  $message = "Hello {$firstname},\nPlease confirm your signup by
              going here:\n
              This will expire in 72 hours.";
  mail($to, "Signup for example.com", $message, "From: [email protected]");
If that id exists in the database then mark it active.  Either compare 
the date (72 hours difference) of if you decided to DELETE unconfirmed 
records (in the cronjob below) simply check to see if it exists.
For example (this needs editing, proper error reporting, etc. as it's 
only an example that shows the concept!):
  $id = @trim($_GET['id']);
  if (strlen($id) !== 32) {
      echo "Sorry, improper ID";
  } else { 
      $sql = "SELECT * FROM mytable WHERE uniqueid = '{$id}';
      $result = mysql_query($sql);
      if (!$result) {
          echo "Could not run query";
      if (mysql_num_rows($result) > 0) {
          $row = mysql_fetch_assoc($result);
          $sql = "UPDATE mytable 
                  SET active = 1
                  WHERE uniqueid = '$id'";
          $result = mysql_query($sql);
          if (mysql_affected_rows() > 0) { 
              echo "Hello {$row['firstname']}, thank you for
                    confirming!  Enjoy!";
          } else {
              echo "For some reason we couldn't confirm";
      } else {
          echo "Sorry we could not find your ID, perpahs it expired?";
d) Check 72 hours period
I'd most likely use a cronjob* here that is run about every hour that 
deletes unconfirmed submissions that are at least 72 hours old.  If 
you're unable to setup a cronjob then you can simply check the 
timestamp, and/or run the delete check everytime someone executes a 
certain page, like confirm.php or submit.php  The SQL for that may 
look something like this:
  $sql = "DELETE FROM mytable 
          WHERE timestamp < (timestamp - (60*60*72)) AND active = 0";
That's one way that comes to mind at least! :)
* How to setup a cronjob (Linux):