View Full Version : Timed Registered Account
08-07-2008, 06: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, 08: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, 08:05 PM
Ok, thanks. So should I code a query into the php file to subtract the two dates?
08-07-2008, 08:07 PM
Yep-- look at the DATE_SUB function from the link.
08-07-2008, 08:46 PM
How do I incorporate that into a query?
08-07-2008, 09: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, 09: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, 09:52 PM
SELECT DATE_ADD(dateField, INTERVAL 1 DAY) FROM table
08-07-2008, 10: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);
$account_time = $query_row["0"];
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, 10: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, 10: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, 10: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, 10: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, 12: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.