query problems with Time field

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

query problems with Time field

Post by Swede78 »

I'm having trouble getting my query statement to work. Everytime someone logs into my site, I record their info (including date and time separately) into a mysql database. I'm now building a page that shows all the people that have logged in within the past 10 minutes.

I can select people who's login date is less than 1 day ago, but the login time part doesn't work.

Code: Select all

<?php
$query = "SELECT * from members WHERE LoginDate <= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND LoginTime <= DATE_SUB(CURTIME(), INTERVAL 10 MINUTE)";
?>
It seems to work for the date part when I remove the code for the time part. But, I want to only show people who have logged in within the last 10 minutes.

LoginDate is in a date field as YYYY-MM-DD and LoginTime is in a time field as HH:MM:SS.

Any help would be greatly appreciated.
Gen-ik
DevNet Resident
Posts: 1059
Joined: Mon Aug 12, 2002 7:08 pm
Location: London. UK.

Post by Gen-ik »

I personally use the UNIX timestamp for this sort of stuff as it gives you a nice round number (in seconds) to play with.

If I was doing what you are doing I would probably go about it like this...

Code: Select all

<?php
/* Once the user has logged in get the current UNIX timestamp and include that in the database somewhere */

$timenow = time();
mysql_query(" INSERT INTO table VALUES ('$username','$timenow') ");

/* now if you want to check for people who have logged in in the last 10 minutes you would do something like this... */

$time = time(); // get the UNIX timestamp
$ten_minutes_ago = $time - 600; // 600 seconds = 10 minutes
$members = mysql_query(" SELECT * FROM table WHERE `timenow`>'$ten_minutes_ago' ");
?>
Don't forget to make the field in the database where you store the UNIX timestamp an INT field otherwise it will simply treat the numbers as a string and not a valid number (int).

Hope this helps? 8)
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

The only problem is that I set up the login database months ago. So, I'd need to convert all the current data stored as date and time fields to unix timestamp.

Instead, I found a way to do this using the date and time data as is.

Code: Select all

<?php
$query = "SELECT * FROM table WHERE ((LoginDate*1000000+LoginTime) >= (NOW()-600))";
?>
This seems to work ok. Thanks for your reply.
Post Reply