View Full Version : Timed Registered Account

08-07-2008, 07:49 PM
Ok, so I am trying to code something to where when people register, they can pay for a membership. They can pay for 30 days, 60 days or 90 days. (This is only an example.)

How would I be able to code it to where it will check to see if there account has expired? I have already coded the registration and log in. And there is a timestamp submitted to the MySQL db, as date(ymdHis).

So how should I do this?

08-07-2008, 09:03 PM
The date time functions of mysql is all you need, check them out:

Post back if you run into issues...

08-07-2008, 09:05 PM
Ok, thanks. So should I code a query into the php file to subtract the two dates?

08-07-2008, 09:07 PM
Yep-- look at the DATE_SUB function from the link.

08-07-2008, 09:46 PM
How do I incorporate that into a query?

08-07-2008, 10:06 PM
This should push you in the right direction:
select datediff(now(), dateField) from table

Run that with your table + fields and figure out what to put into the where clause.

08-07-2008, 10:44 PM
Ok, that worked.

But now, I am having a problem. The person asking me to code this has asked to let them sign up for a 1 day free trial.

Heres the thing, when someone signs up for it, if they sign up at 11:59 PM, they only get a 1 minute free trial, instead a full 24 hours. How should I fix this?

08-07-2008, 10:52 PM

08-07-2008, 11:05 PM
//Gets information about account
$get_email_data = "SELECT * FROM online_videos WHERE email='$email'";
$get_email_result = mysql_query($get_email_data, $datacon);
$email_row = mysql_fetch_assoc($get_email_result);

$account_init_time = $email_row['init_time'];
$account_duration_days = $email_row['account_duration_days'];

$subtract_date_query = "SELECT DATE_SUB(NOW(), INTERVAL '" .$account_init_time . "', DAY)";
$query_result = mysql_query($subtract_date_query);
$query_row = mysql_fetch_row($query_result);
echo mysql_error($datacon);
$account_time = $query_row["0"];
echo $account_time;
if($account_time >= $account_duration_days)

When that is executed, I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' DAY)' at line 1

But according to the manual, DAY is where it should be...

08-07-2008, 11:11 PM
$subtract_date_query = "SELECT DATE_SUB(NOW(), INTERVAL " .$account_init_time . " DAY)";

echo out $subtract_date_query and run in mysql to debug.

08-07-2008, 11:21 PM
I entered in that code, and I got this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '23:58:56 DAY)' at line 1

I am running MySQL 6.02

08-07-2008, 11:24 PM
Echo out the query and debug it. It looks like you are putting in the wrong variable for the interval. It should be an integer number....e.g. 1 day, 2 day you get the idea.

08-07-2008, 11:34 PM
So, it doesn't actually subtract two dates? It just adds/subtracts to them.

The db has the timestamp from when they registered, and then stores how many days they get.(In this case, 1).

How should I do this then? Is there a way to do this php side?

08-08-2008, 01:57 AM
Sorry for the double post.

Ok, I figured it out. Instead of expressing the account_duration column in days, I express it in hours. And instead of using DATEDIFF, I use TIMEDIFF.