Comparing date from mysql table 2

[richclever]

I have a table on my mysql database which hold an expiry date in mysql's date format. I need to compare it to the current server date to check if a username has expired.

I have set date is php to the same format as the mysql date using

$today = date("Y-m-d");

I am trying to check the system date using

if ($expirydate < $today) {
do something
}


but it just won't work.

What is the proper way to validate?

Thanks in advance.
Richard

 

[Vragabond]

When date comes from MySQL it is a string and so cannot really be easily compared to dates. Use strtotime() to convert it back to unix timestamp.

 

[DRJ478]

Let the MySQL server do the thinking.
Does the server have the same date and time as the web server (same machine)?
Just put in your SELECT query the condition that the expiration date is < NOW(). That will do it. A garbage collection routine can easily take care of the expired records, a quick query to delete all records that have expired is all needed.

 

[richclever]

Hi Drj,

Please excuse my ignorance, but could you clarify how to construct the sql query. Currnetly I have

$userquery="select * from users where (username = '$login' and password = '$pass')";

as my mysql query so it just queries for a valid login and password combination. I need it also to check that the expiry date in the same table is earlier than the current date so it can authenticate.

Many thanks, I am now going to thankyou yet again on tektips for a very valuable answer.

Richard

 

[DRJ478]

$userquery="SELECT * FROM users WHERE username = '$login' AND password = '$pass' AND expirationdate < NOW()";

The above query will return a row if all three conditions apply.

However,

expiry date in the same table is earlier than the current date

This statement would mean to me that the users record is expired. Wouldn't you rather look for records that have not yet expired?

 

[richcleverley]

Hi DRJ,

Thanks for the tip again. Works like a dream, and yes I did make the mistake of looking for expirydate to be lower than todays date. Works great with > instead of <.

Problems with my usual profile so had to create a new one!!

Richard

 

[DRJ478]

A last note:
I would not recommend to tuen < into > but rather into >= to allow the person to access the site if this is the last day of validity. Expires on mm/dd/yyyy ussually means that one still is able to access the very day of expiry.

 

[richcleverley]

Already thought of that. Thanks very much.

Richard