Compare two dates from database

[four0four]

I'm trying to create a basic member subscription system that compares when an account was created and when it expires. If it expires it updates the account status to "0".

How can I query and compare two dates from my database?

I have one column called "created" and another called "expires".

When the account expires the column called "active" is changed to "0".

I'm still new to MySQL so any help would be appreciated.

Thanks!

[Fumigator]

Why do you have to compare the two dates? If you have a date that represents the date the account expires, isn't it enough to check to see if that expires date is in the past or is in the future? For that matter, why have an "active" flag, when the expires date accomplishes the very same thing as the active flag?

Anyway... you will still need to compare the expires date to today's date. You first of all must be sure your expires date is a "date" or "datetime" or "timestamp" data type. If you used char, or varchar, to store the date, you won't be able to use the date functions built into MySQL.

Assuming the expires date is a datetime, you can use a simple comparison against CURDATE().


SELECT * FROM yourtable
WHERE expires_date < CURDATE()

[four0four]

Why do you have to compare the two dates? If you have a date that represents the date the account expires, isn't it enough to check to see if that expires date is in the past or is in the future? For that matter, why have an "active" flag, when the expires date accomplishes the very same thing as the active flag?

Anyway... you will still need to compare the expires date to today's date. You first of all must be sure your expires date is a "date" or "datetime" or "timestamp" data type. If you used char, or varchar, to store the date, you won't be able to use the date functions built into MySQL.

Assuming the expires date is a datetime, you can use a simple comparison against CURDATE().


SELECT * FROM yourtable
WHERE expires_date < CURDATE()


Well, I mainly have two dates for my own personal records to see exactly when an account expires and I need to send out reminder notices 7 days before an account expires if payment hasn't been received.

I have an active status to interact with IPN and to temporarily suspend an account if there's too many failed login attempts.

Mind you, I'm still new to MySQL so I'm still learning. :)

But if I did want to compare two columns from my table, would I need to make two separate queries? Or can I just make one? I'm using PHP as well.

[Coyote6]

Fumigator is right. I do not see the need to change your active status if the account expires. I understand the need to have the active status for your login but it is just going to be extra steps turning this switch on and off for an expiration feature.

For the 7 day notification part you would use.

SELECT * FROM yourtable
WHERE expires_date = DATE_ADD(CURDATE(), INTERVAL 7 DAY);


For finding the expired ones you would use Fumigators.

[four0four]

Fumigator is right. I do not see the need to change your active status if the account expires. I understand the need to have the active status for your login but it is just going to be extra steps turning this switch on and off for an expiration feature.

For the 7 day notification part you would use.

SELECT * FROM yourtable
WHERE expires_date = DATE_ADD(CURDATE(), INTERVAL 7 DAY);


For finding the expired ones you would use Fumigators.

I see, that makes sense. If it makes things more efficient, then I'm happy with that.

Can you think of another way I can control login access without using some sort of status indicator?

Basically, if a member's account has expired they would just be taken to a page that prompts them to make a payment to continue their subscription, otherwise if their account is still active they are taken to the member's area.

[Coyote6]

Ok so it would take two different queries doing it this way but I think I have what you need.


// Get the password and username from the login form.
$username = $_POST['username'];
$password = $_POST['password'];

###### BE SURE TO VALIDATE YOUR DATA HERE ######

$q = "SELECT * " .
"FROM `users` " .
"WHERE `expires_date` > CURDATE() && `active`=1 && `password`='$password' && `username`='$username'";
$r = @mysql_query ($q);
// Make sure that a username is unique of course. If it is only 1 result or none will be found.
if (mysql_num_rows($r) == 1) {
// Take the user to their home page whatever url that maybe.
header ('Location: http://www.yoursite.com/user/home.php');
exit();
}
else {
$q = "SELECT * " .
"FROM `users` " .
"WHERE `expires_date` <= CURDATE() || `active`=0 && `password`='$password' && `username`='$username'";
$r = @mysql_query ($q);
if (mysql_num_rows($r) == 1) {
// Redirect the user to the renewal page... be sure to let them know that on this page that if there account needs renewal or if their account was disabled due to the failed logins or what not.
header ('Location: http://www.yoursite.com/user/renewal.php');
exit();
}
else {

###### Update whether it was a failed login or if the count doesn't exit... ######

}
}

[Old Pedant]

I don't see the need for two queries!

How about this:

// Try to never do SELECT * -- it's really wasteful of resources
// only get the fields from db that you need...
// is you won't use expires_date directly, don't get it.
// but do get subscription_status:
$q = "SELECT expires_date,IF(expires_date < CURDATE(), 'expired','okay') AS subscription_status " .
"FROM `users` " .
"WHERE `active`=1 && `password`='$password' && `username`='$username'";
$r = @mysql_query ($q);
// Make sure that a username is unique of course.
// easy way to do that is to make it the primary key
if (mysql_num_rows($r) == 0 )
{
// not a valid user account of any kind
header ('Location: http://www.yoursite.com/login.php'); // try logging in again, maybe??
exit();
} else {
// is a valid user account, so read the value of subscription_status
// to find out if they are expired or okay...
$stat = ... however you read that value from record in PHP ...
if ( $stat == "okay" )
{
// Take the user to their home page whatever url that maybe.
header ('Location: http://www.yoursite.com/user/home.php');
exit();
} else {
header ('Location: http://www.yoursite.com/user/renewal.php');
exit();
}
}

Not a PHP user, sorry, but that's irrelevant. The logic of this is clear.

[four0four]

Thanks guys for those examples! That really steers me in the right direction as to how I can control login access! :)

Now, if some users haven't logged in for a while, how should I automatically check the expiration date of each user and then change the status to "0" if they haven't payed?

Should I just incorporate your examples into a cron job?

Thanks again! I appreciate your help.

[Old Pedant]

how should I automatically check the expiration date of each user and then change the status to "0" if they haven't payed [sic]?
Why? What's the point?

If they are expired, does it really matter if they expired 10 minutes ago or 10 years ago?? Let them pay their money and become active again.

If you really think you need to change them to inactive after some (long) period of time, then sure, you could do that with a cron job. Just create a ".sql" text file and execute it via the mysql command.

[Coyote6]

I don't see the need for two queries!


OP very nice method for combining them. You never cease to amaze me. :thumbsup: