Timed Registered Account

[metroid]

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?

[Brandoe85]

The date time functions of mysql is all you need, check them out:
http://dev.mysql.com/doc/refman/5.0/...functions.html

Post back if you run into issues...

[metroid]

Ok, thanks. So should I code a query into the php file to subtract the two dates?

[Brandoe85]

Yep-- look at the DATE_SUB function from the link.

[metroid]

How do I incorporate that into a query?

[Brandoe85]

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.

[metroid]

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?

[Brandoe85]

Try:
SELECT DATE_ADD(dateField, INTERVAL 1 DAY) FROM table

[metroid]

PHP Code:

//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)
{
session_destroy();
header("location:videos.php?msg=expired");
} 


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...

[Brandoe85]

$subtract_date_query = "SELECT DATE_SUB(NOW(), INTERVAL " .$account_init_time . " DAY)";

echo out $subtract_date_query and run in mysql to debug.

[metroid]

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

[Brandoe85]

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.

[metroid]

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?

[metroid]

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.