Time Based Account

Shockdot · August 26, 2010

Hello everyone, here is my problem.

I am working for someone that would like users to be able to create a Trial Account on their website. This trial account is to be used for 24 hours, once the 24 hours is completed they can no longer login/use that account.

Everything about the Trial Account is completed, other then the account timer.

I am at a loss on how to get this part done.

I was thinking that I would use the MySQL commands, GETDATE() and DATEADD(), to get the date and time that the account was created and using the DATEADD() command I would add 24 hours to the GETDATE() value. Then if the user logs into at a date past the DATEADD() value the Users Account type is switched to 3(This means that the account has been disabled) and then is directed to the Logout page, which then redirects him back to the homepage.

After this point, the user can no longer login due to the code preventing any user with an account type of 3 to login.

The problem is that I am unaware how to incorporate the GETDATE() and DATEADD() functions into my PHP code.

Would anyone be able to explain how I can get this done, or suggest a better method of inputting a starting time and ending time for the account?

Thank You for any/all help.

MadTechie · August 26, 2010

Try this

Part of the SQL

WHERE NOW() < DATEADD(`createdTime`,  INTERVAL 1 DAY)

Shockdot · August 26, 2010

Try this

Part of the SQL

WHERE NOW() < DATEADD(`createdTime`,  INTERVAL 1 DAY)

I'm confused on how to place this into my PHP code. Could you explain it some more?

Normally, I only use WHERE to locate a section in my SQL tables.

would this go something along the lines of,

mysql_query="INSERT INTO Accounts (Name) VALUES ('$Name') WHERE NOW() < DATEADD(`createdTime`,  INTERVAL 1 DAY)";

I'm not sure how that would work seeing as there is not an already stored value, nor do i know how this would find the values I'm looking for :/.

MadTechie · August 27, 2010

That's the WHERE part of the SQL, just add it to the login query!

Its very hard for me to say how to incorporate it without knowing anything about your current system.

Shockdot · August 27, 2010

That's the WHERE part of the SQL, just add it to the login query!

Its very hard for me to say how to incorporate it without knowing anything about your current system.

Here is my code, I'm not sure how the code would help me. Maybe this could help you explain.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Trial Account Signup</title>
</head>

<body>
<?php

require_once("../Config.php");

mysql_connect($Host, $SQLUsername, $SQLPassword) or die("We can't connect to the MySQL Server at this time. Please try again later.");
mysql_select_db($SQLDataBaseName) or die("We connected to the MySQL Server, but we were unable to connect to the assigned Database. Please try again later.");

$GuestEmail = $_POST['GuestEmail'];
$FirstName = "Guest";

///////////////////////////////////////////////////////////////////
///////////////////////Random Username Start///////////////////////
///////////////////////////////////////////////////////////////////
//Random String - Will specify start of starting string.
$RandomUsername = "Trial_";

//Random String Length
$RandomUsernameLength = 10;

//Random String Character Restrictions - Can only have characters listed in the variable.
$RandomUsernameRestrictions = "abcdefghijklmnopqrstuvwxyz0123456789";


//While the RandomUsernameLength is greater then 0 run bellow code.
while($RandomUsernameLength > 0)
{
//Selects a random character from the RandomUsernameRestrictions variable and adds it to the RandomUsername variable.
$RandomUsername .= substr($RandomUsernameRestrictions,rand(0,strlen($RandomUsernameRestrictions)),1);

//Reduces the RandomUsernameLength variable by 1 so that the loop will eventually end.
$RandomUsernameLength -= 1;
}

///////////////////////////////////////////////////////////////////
///////////////////////Random Password Start///////////////////////
///////////////////////////////////////////////////////////////////
//Random String - Will specify start of starting string.
$RandomPassword = "";

//Random String Length
$RandomPasswordLength = 10;

//Random String Character Restrictions - Can only have characters listed in the variable.
$RandomPasswordRestrictions = "abcdefghijklmnopqrstuvwxyz0123456789";


//While the RandomPasswordLength is greater then 0 run bellow code.
while($RandomPasswordLength > 0)
{
//Selects a random character from the RandomPasswordRestrictions variable and adds it to the RandomPassword variable.
$RandomPassword .= substr($RandomPasswordRestrictions,rand(0,strlen($RandomPasswordRestrictions)),1);

//Reduces the RandomPasswordLength variable by 1 so that the loop will eventually end.
$RandomPasswordLength -= 1;
}

$UserIP = $_SERVER['REMOTE_ADDR'];
$AccountType = 2;
$DateCreated = date("m/d/y");

$EmailCheck = mysql_query("SELECT Email FROM Accounts WHERE Email = '$GuestEmail'");
$GuestEmailCheck = mysql_query("SELECT Email FROM Accounts WHERE GuestEmail = '$GuestEmail'");
$GuestUsernameCheck = mysql_query("SELECT Email FROM Accounts WHERE Email = '$RandomUsername'");

if (mysql_num_rows($EmailCheck) > 0)
{
header("Location: ../Trial/TrialFailed.php");
}
elseif (mysql_num_rows($GuestEmailCheck) > 0)
{
header("Location: ../Trial/TrialFailed.php");
}
elseif (mysql_num_rows($GuestUsernameCheck) > 0)
{
header("Location: ../Trial/TrialUsernameFailed.php");
}
else
{
echo($RandomPassword);
$RandomDBPassword = md5($RandomPassword);
mysql_query("INSERT INTO Accounts (Email, FirstName, GuestEmail, Password, UserIP, AccountType, DateCreated) VALUES ('$RandomUsername', '$FirstName', '$GuestEmail', '$RandomDBPassword', '$UserIP', '$AccountType', '$DateCreated')");
}
?>
</body>
</html>

MadTechie · August 27, 2010

All the sign code tells me is that the field is `DateCreated`

WHERE NOW() < DATEADD(`DateCreated`,  INTERVAL 1 DAY)

I don't know what you want to do, or how you want to implement it, only you can decided that!

Shockdot · August 27, 2010

All the sign code tells me is that the field is `DateCreated`
WHERE NOW() < DATEADD(`DateCreated`,  INTERVAL 1 DAY) 
I don't know what you want to do, or how you want to implement it, only you can decided that!

Date Created is a php code, not sql based, that just finds the month day and year that the account is created.

I need to make it find the time, and then have it add 24 hours to that time into another variable. then store both variables.

Andy-H · August 27, 2010

So when the user logs in:

$query  = "SELECT COUNT(*) FROM Accounts WHERE NOW() < DATEADD(`DateCreated`,  INTERVAL 1 DAY) AND Email = '" . mysql_real_escape_string($_POST['email']) . "' LIMIT 1";
$result = mysql_query($query)or trigger_error(mysql_error(), E_USER_ERROR);
$row    = mysql_fetch_row($result);

   if ($row[0] === 1)
   {
      $error = "Guest account has expired.";
   }
   else
   {
      header("Location: logged_in_page.php");
   }

Shockdot · August 27, 2010

So when the user logs in:

$query  = "SELECT COUNT(*) FROM Accounts WHERE NOW() < DATEADD(`DateCreated`,  INTERVAL 1 DAY) AND Email = '" . mysql_real_escape_string($_POST['email']) . "' LIMIT 1";
$result = mysql_query($query)or trigger_error(mysql_error(), E_USER_ERROR);
$row    = mysql_fetch_row($result);

   if ($row[0] === 1)
   {
      $error = "Guest account has expired.";
   }
   else
   {
      header("Location: logged_in_page.php");
   }

Maybe I'm missing something...

How does SQL know the DATEADD value for that selected section, if the DATEADD value has not been called and stored?

Also, how does this determine the value of the account when trying to login?

MadTechie · August 27, 2010

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Shockdot · August 27, 2010

It's late and I've been at this for awhile now...

Here is my login code.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>

<?php
require_once("MBLConfig.php");

// username and password sent from form
$MBLEmail = $_POST['Email'];
$MBLPassword = md5($_POST['Password']);

// To protect MySQL injection (more detail about MySQL injection)
$MBLEmail = stripslashes($MBLEmail);
$MBLPassword = stripslashes($MBLPassword);
$MBLEmail = mysql_real_escape_string($MBLEmail);
$MBLPassword = mysql_real_escape_string($MBLPassword);

$NameSQL = "SELECT * FROM $SQLTableName WHERE MBLEmail='$MBLEmail' and MBLPassword='$MBLPassword'";
$NameResult = mysql_query($NameSQL);
$Row = mysql_fetch_array($NameResult);

$Query = "SELECT * FROM $SQLTableName WHERE MBLEmail='$MBLEmail' and MBLPassword='$MBLPassword'";
$Result = mysql_query($Query);

$Count = mysql_num_rows($Result);

if($Count == 1)
{
session_start();
$_SESSION['MBLFirstName'] = $Row['MBLFirstName'];
$_SESSION['MBLPassword'] = 'MBLPassword';
header("location:../");
} else {
header("location:../InvalidLogin.php");
}
?>

</body>
</html>

Perhaps one of you can explain exactly how the code you both provided "WHERE NOW() < DATEADD(`DateCreated`, INTERVAL 1 DAY)", can be incorporated...

Shockdot · August 27, 2010

Sorry for the double post, but I`m still needing help with this.

Sign In

Time Based Account

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information