[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Creating/Removing a temporary account into/out of SQL database.  PHP

Posted on 2007-07-23
12
Medium Priority
?
308 Views
Last Modified: 2013-12-13
Hi.
On my website, I currently have three user "access levels" for a user.  These user levels are determined by the administrator.  Based on who the user is, the access level allows a user to access different pages.

I was thinking this:

Currently, I have a registration page where an administrator can create an account for a user by entering a username, assigning a password, confirmed password, and email address.  After submission, an email is sent out to the user's input email address with a confirmation link to verify the account.  After verified, the account is set to active.  A user cannot login unless their account is set as active.

 What I am looking for is a way to create a 1 day temporary account.  I was thinking I could add a checkbox under the registration fields that says something like, Is this a temporary account?  If the checkbox is marked, it sets the 'Level_access' for the user to level 4 instead of the default (level 2) upon submission.  

The tricky part to this is if the account is marked as a temporary account (level 4), after submission and email authentication,  the account needs to be active for one day only.  Once a 24 hour period has passed, the account should be deleted from the database automatically.  

 I have attached the code of my registration page in case you need to see it!

I appreciate any help!!! Thanks!

Register_user.php
============================
<?php
session_start();
include_once('log.php');

if (!isset($_SESSION['logged_in'] ))
{
    header("Location: access_denied.php");
}

if ($_SESSION['Level_access'] != 1)
{
    header('Location: access_denied.php');
}

require_once('db.php');
include('functions.php');

      if(isset($_POST['register']))
      {
            if($_POST['username']!='' && $_POST['password']!='' && $_POST['password']==$_POST['password_confirmed'] && $_POST['email']!='' && valid_email($_POST['email'])==TRUE && checkUnique('Username', $_POST['username'])==TRUE && checkUnique('Email', $_POST['email'])==TRUE)
            {
            
                  $query = mysql_query("INSERT INTO users (`Username` , `Password`, `Email`, `Random_key`) VALUES ('".mysql_real_escape_string($_POST['username'])."', '".mysql_real_escape_string(md5($_POST['password']))."', '".mysql_real_escape_string($_POST['email'])."', '".random_string('alnum', 32)."')") or die(mysql_error());
                  
                  $getUser = mysql_query("SELECT ID, Username, Email, Random_key FROM users WHERE Username = '".mysql_real_escape_string($_POST['username'])."'") or die(mysql_error());
      
                  if(mysql_num_rows($getUser)==1)
                  {                  
                        $row = mysql_fetch_assoc($getUser);
                        $headers =       "From: Dialer Secure Server";
                      $headers =      "Reply-To: something@something.com";
                        $subject = "Activation email for Dialer Secure Server";
                        $message = "Dear ".$row['Username'].", this is your activation link to be added to the Dialer Secure Server Database. In order to confirm your user account, please click on the following link: http://*.*.*.*/dialer/confirm.php?ID=".$row['ID']."&key=".$row['Random_key']." \n\n Please remember to change your password upon first login!!!\n\n Thank you for joining!";
                        if(mail($row['Email'], $subject, $message, $headers))
                        {
                              header('Location: registration_complete.php');
                        }
                        else {
                              header('Location: registration_error.php');
                        }
                  }
                  else {
                        header('Location: registration_error.php');
                  }
                                          
            }
            else {            
                  header('Location: registration_error.php');      
            }
      }
?>
<?php if(isset($error)){ echo $error;}?>
<?php if(isset($msg)){ echo $msg;} else {}//if we have a mesage we don't need this form again.?>


Here is the form, in case you need that as well:
=========================================
<form name="form1" method="post" action="">
              <table width="94%">
                <tr>
                  <td height="24"><div align="center"><font color="#000000" size="-2" face="Arial, Helvetica, sans-serif"><img src="http://*.*.*.*/dialer/graphics/logo.JPG" width="14" height="14"></font></div></td>
                  <td height="24"><font color="#000000" size="-2" face="Arial, Helvetica, sans-serif"><strong>Register
                    a member to the database</strong></font></td>
                  <td width="39%" rowspan="7"><font size="-2" face="Arial, Helvetica, sans-serif">To
                    create another user account, please enter appropriate data
                    into the data fields. This information will be stored in the
                    database.</font></td>
                </tr>
                <tr>
                  <td width="11%" height="24"> <div align="right"><font color="#FF0000" size="-2" face="Arial, Helvetica, sans-serif">Username:</font></div></td>
                  <td width="50%"><input type="text" id="username" name="username" size="32" value="<?php if(isset($_POST['username'])){echo $_POST['username'];}?>" />
                    &nbsp;</td>
                </tr>
                <tr>
                  <td height="24"> <div align="right"><font color="#FF0000" size="-2" face="Arial, Helvetica, sans-serif">Password:</font></div></td>
                  <td width="50%"><input type="password" id="password" name="password" size="32" value="" />
                    &nbsp;</td>
                </tr>
                <tr>
                  <td height="26"> <div align="right"><font color="#FF0000" size="-2" face="Arial, Helvetica, sans-serif">Retype
                      Password::</font></div></td>
                  <td width="50%"><input type="password" id="password_confirmed" name="password_confirmed" size="32" value="" />
                    &nbsp;</td>
                </tr>
                <tr>
                  <td height="24"> <div align="right"><font color="#FF0000" size="-2" face="Arial, Helvetica, sans-serif">Email:</font></div></td>
                  <td width="50%"><input type="text" id="email" name="email" size="32" value="<?php if(isset($_POST['email'])){echo $_POST['email'];}?>" />
                    &nbsp;</td>
                </tr>
                <tr>
                  <td height="26">&nbsp;</td>
                  <td height="26"><input type="checkbox" name="checkbox" value="checkbox">
                    <font size="-2" face="Arial, Helvetica, sans-serif">Is this
                    a temporary account?</font></td>
                </tr>
                <tr>
                  <td height="26"> <div align="center"> &nbsp;</div></td>
                  <td height="26"> <input type="submit" name="register" value="register" />
                    &nbsp;</td>
                </tr>
              </table>
            </form>
0
Comment
Question by:dialeradmin
  • 7
  • 5
12 Comments
 
LVL 4

Expert Comment

by:nysurf1
ID: 19548030
I suggest you create a regular account and add to the database a boolean parameter 'temporary' which will be true when the user selects the checkbox. (I imagine you also keep the creation date)
To handle the accounts (deleting the temporary ones) you need to use a stored procedure - a script that is located on the server and runs in schedualed times . this procedure can run every hour and go over all the table of the accounts , check which is temporary and if 24 hours have passed , delete it.
http://articles.techrepublic.com.com/5100-22-5178706.html
0
 

Author Comment

by:dialeradmin
ID: 19548264
Ok, this sounds like its going to be really complex.  I'll start off small and build my way up...
Here is where I'll begin, haha.

I added a field in my SQL database called 'temporary' ... I set the type as BOOL.  If it needs to be set to true when the box is checked, what would I need to put in for the Value, or Default?  Anything?

I currently do not have a field set up to track the creation date of when an account is registered.  I will have to restructure my register page to insert into my SQL database the creation time after a user is registered.  To do this, should I just create another field called 'date_time' and set it as type: DATETIME?  What would I need to put in for the rest of this field?

I guess I'll start there...
0
 
LVL 4

Expert Comment

by:nysurf1
ID: 19554112
There are many ways do do things , this is the way i would do it :
1. set the temporary column in the database to be false by defaoult.
2. the creation time would be a type date , search you sql aplication for 'timestamp' which is automaticly inserts the date when the data is entered.


these are the first steps , it's not so complex but we can do it in small steps if u preffer
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dialeradmin
ID: 19555914
Ok.  If you have time, small steps would help me understand things better...but really, any help is appreciated!!!

I have the fields set up in the SQL statement.  Just to make sure real fast though, you said the 'temporary' should be set to type BOOL, right?

So now, it looks as if the next step is to INSERT INTO my SQL the 'timestamp', correct?  Here is what I've tried...Is the INSERT INTO for 'Timestamp' correct?  I'm not too sure about the GETDATE() I used???
===========================================

if(isset($_POST['register']))
      {
      if($_POST['username']!='' && $_POST['password']!='' && $_POST['password']==$_POST['password_confirmed'] && $_POST['email']!='' && valid_email($_POST['email'])==TRUE && checkUnique('Username', $_POST['username'])==TRUE && checkUnique('Email', $_POST['email'])==TRUE)
            {
            
            $query = mysql_query("INSERT INTO users (`Username` , `Password`, `Email`, `Random_key`, `Timestamp`) VALUES ('".mysql_real_escape_string($_POST['username'])."', '".mysql_real_escape_string(md5($_POST['password']))."', '".mysql_real_escape_string($_POST['email'])."', '".random_string('alnum', 32)."', '".GETDATE()."')") or die(mysql_error());



Also, as far as a temporary account being created when the temporary checkbox is clicked during registration, what are some steps for altering my code (my very first post) to get this to mark the account as temporary?

Thanks!
0
 
LVL 4

Accepted Solution

by:
nysurf1 earned 1500 total points
ID: 19556394
I ment that you use the mysql timestamp - http://dev.mysql.com/doc/refman/5.0/en/datetime.html but your aproach to getting the date from the php will work as well , i would change the name of the row TimeStamp to Creation_Date (might be a word used by mysql)
next step is to add a check-box to your form and see what value you get back when it is checked and accordingaly add the value to the mysql
echo($_POST['checkbox']);
0
 

Author Comment

by:dialeradmin
ID: 19556862
Alright.  I changed the table in my database to include 'Creation_date' with type: DATETIME.
 - What should I enter into the INSERT INTO statement to get the 'Creation_date' updated during registration?

Here is what I have now:
$query = mysql_query("INSERT INTO users (`Username` , `Password`, `Email`, `Random_key`, `Creation_date`) VALUES ('".mysql_real_escape_string($_POST['username'])."', '".mysql_real_escape_string(md5($_POST['password']))."', '".mysql_real_escape_string($_POST['email'])."', '".random_string('alnum', 32)."')") or die(mysql_error());


And as far as adding a checkbox to the form, I've done that as well...
 - From here I'm confused on where to go when trying to make the account a 1-day account if the checkbox is clicked.
0
 
LVL 4

Expert Comment

by:nysurf1
ID: 19567078
Thanks ,
did you manage ?
0
 

Author Comment

by:dialeradmin
ID: 19567132
I have everything working except for a script that runs every hour and checks the 'users' database for any record marked '1' in the 'Temporary' field.  If the record's 'Creation_date' is past 24 hours, it deletes that record....

Any ideas? haha
0
 
LVL 4

Expert Comment

by:nysurf1
ID: 19567210
Nice work !! few options ahead , easiest one will be writnig the procedure from the database , what db are you using ?
0
 

Author Comment

by:dialeradmin
ID: 19567238
I'm using a database called 'users' in MySQL client version: 5.0.27
0
 
LVL 4

Expert Comment

by:nysurf1
ID: 19567745
0
 
LVL 4

Expert Comment

by:nysurf1
ID: 19567754
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month19 days, 2 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question