Compare date from MySQL to date from PHP

Question

I have a web app which users login to it. Every time a specific user logs in, I store the login datetime in a MySQL db:

(yyyy-mm-dd hh-mm-ss)

Each record in the db has a user_identifier and last_login_datetime.
However, I want to make sure that users can only login once a day.
I was thinking about comparing the time function in PHP to the datetime I have in the DB and see if the user was already logged on today.
If the user was logged on today, I'll kick him out.

How do I compare the record in the DB to the time function in PHP to see if the user was logged in already?

Thanks!

Why don't you do this entirely in the DB? It has all the date manipulation and comparison functions you could want. — Kerrek SB
– Kerrek SB, Commented Jul 11, 2011 at 13:26
SELECT * from users WHERE date_add(last_login_datetime, interval 1 day) < now() and username="" and password=... — Lawrence Cherone
– Lawrence Cherone, Commented Jul 11, 2011 at 13:36
You could do something like SELECT logon_date=CURDATE() AS kick_out FROM users where user_id=xxx. — Michael Mior
– Michael Mior, Commented Jul 11, 2011 at 13:36
"can only login once a day" - but what does this mean? Once every 24 hours? Once per calendar day? If the latter, which calendar day - the one on the server? The UTC calendar day? The one on the the client? — symcbean
– symcbean, Commented Jul 11, 2011 at 13:42

deefour · Accepted Answer · 2011-07-11 13:36:45Z

1

You might add something like this to conditions of the SELECT to validate a user's credentials when they login

... (AND DAY(last_login_datetime) != DAY(NOW()) OR MONTH(last_login_datetime) != MONTH(NOW()) OR YEAR(last_login_datetime) != YEAR(NOW()))

If the day, month, or year do not match today's day, month, and year, the select will return the user (assuming the other credentials/conditions are valid). If no result is returned, the user's credentials were incorrect or the user has already logged in today - either way they should not be allowed to login according to your requirements.

answered Jul 11, 2011 at 13:36

deefour

35.4k7 gold badges99 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Swift · Accepted Answer · 2011-07-11 13:37:41Z

1

I know it's not quite what you've asked for, but this can be done with strictly just SQL

SELECT * FROM table WHERE DATEDIFF(created_on, NOW()) <= -1

This will return any users who have logged in more than 1 day ago. Of course you would limit it to the specific user you want and if the result is empty then deny them login privileges.

answered Jul 11, 2011 at 13:37

Swift

13.2k5 gold badges58 silver badges80 bronze badges

Comments

Michael J.V. · Accepted Answer · 2011-07-11 13:41:35Z

If you need to compare dates in PHP for whatever reason, the best way to go about it is using DateTime, DateTimeZone and DateInterval classes.

Reason for that is ease of calculating the differences, constructing the date object from various input dates and most important - time zones are taken into account too so you won't make a mistake when dealing with daylight savings.

How to:

$date = '2011-07-11 13:37:37';
$timezone = 'Europe/London';

$date = DateTime::createFromFormat('yyyy-mm-dd hh-mm-ss', $date, new DateTimeZone($timezone );
$now = new DateTime(time(), $timezone);

$diff = $date->diff($now); // You got the difference here. If the difference is less than 24h then attempt in the same day occurred.

if($diff->h < 24)
{
    // failure
}

qbert220 · Accepted Answer · 2011-07-11 13:31:46Z

0

If you want to do this in PHP, you can use strtotime() to convert to a timestamp (number of seconds since 1/1/1970), then compare as you want:

$time = strtotime($row['time']);

answered Jul 11, 2011 at 13:31

qbert220

11.6k4 gold badges34 silver badges33 bronze badges

Comments

WhiskeyTangoFoxtrot · Accepted Answer · 2011-07-11 13:34:26Z

0

Aside from doing it completely on the database side since it is possible (and probably easier to do without shuffling it between SQL and PHP), the best thing you should do when working with formatted timestamps from two different platforms is to convert both timestamp strings to a uniform timestamp.

You can do this with strtotime(). I run into the situation often when I need to compare SQL server timestamps with server timestamps, so I just use good ol strtotime() when I need to bring it on the PHP side.

answered Jul 11, 2011 at 13:34

WhiskeyTangoFoxtrot

6407 silver badges32 bronze badges

Comments

madflow · Accepted Answer · 2011-07-11 13:39:15Z

0

PHP Snippet:

$today = date('Y-m-d');
$dbDate = new DateTime('2011-07-11 12:00:00'); // DB Date as parameter

if($today == date_format($dbDate,'Y-m-d'))
{
    ; // Magic
}

answered Jul 11, 2011 at 13:39

madflow

8,6933 gold badges45 silver badges61 bronze badges

Collectives™ on Stack Overflow

Compare date from MySQL to date from PHP

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related