homepage Welcome to WebmasterWorld Guest from 54.197.215.146
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Unable to select using datetime range
techtheatre




msg:4107602
 2:46 pm on Mar 31, 2010 (gmt 0)

I am using MySql (and PHP) to try to find out if there are any records in my table that are less than 10 minutes old. Basically this is part of a time-keeping script, and I create a new entry if it has been more than 10 minutes (no results) or I update the current entry if it is less than 10 minutes.

Basic table structure (unimportant fields removed):

CREATE TABLE `timesheet` (
`RecordId` int(8) NOT NULL auto_increment,
`Timestamp` datetime NOT NULL,
PRIMARY KEY (`RecordId`)
) AUTO_INCREMENT=56 ;
INSERT INTO `timesheet` VALUES (55, '2010-03-30 18:19:00');

So, I am using this query:

SELECT * FROM timesheet WHERE Timestamp<='DATE_SUB(NOW(), INTERVAL 10 MINUTE)' ORDER BY Timestamp DESC LIMIT 1;

The problem is that it always returns my latest record...even if it is older than 10 minutes.

What am i doing wrong? Thanks!

 

Demaestro




msg:4107660
 4:30 pm on Mar 31, 2010 (gmt 0)

Wouldn't it be:

>= 'DATE_SUB(NOW(), INTERVAL 10 MINUTE)'

techtheatre




msg:4107773
 7:12 pm on Mar 31, 2010 (gmt 0)

I have tried switching the < and > but when i do it never finds a result...even if the time is well under 10 minutes. I just tried again in case i was being brain-dead...but still no luck.

rocknbil




msg:4107829
 8:39 pm on Mar 31, 2010 (gmt 0)

Demaestro is correct. you want >=. But backtick timestamp

... where `Timestamp` . . . .

It **may** be a reserved word, like datetime, int, etc.

techtheatre




msg:4108218
 12:57 pm on Apr 1, 2010 (gmt 0)

I have added backticks...but it still does not work. It finds no results. If i change the > to a < then it finds one result. Here is my full query:

//check to see if we have an active (live) entry in the database
$sql = "SELECT * FROM timesheet WHERE `UserId`='$UserId' AND `TimeType`='out' AND `Timestamp`>='DATE_SUB(NOW(), INTERVAL 10 MINUTE)' ORDER BY `Timestamp` DESC LIMIT 1; " ;
$result = mysql_query($sql, $db);
if( mysql_num_rows($result)==0 )
{
//no live entry, so create a new one
$sql = "INSERT INTO timesheet...
}

rocknbil




msg:4108568
 9:17 pm on Apr 1, 2010 (gmt 0)

What happens when you do . . .

$result = mysql_query($sql, $db) or die(mysql_error());

?

That query should work . . . test it like so

select now();
-> 2010-04-01 14:10:06

select '2010-04-01 14:10:06' >= date_sub(now(),interval 10 minute);

--> 1

When 10 minutes passes, it will return 0. The only thing that can be assumed is one of the other values are incorrect, that is, in `UserId`='$UserId', the actual value of $UserID is not in the database.

On that topic, if $UserID is numeric, remove the quotes when you add the "or die." It may reveal something you're assuming. :-) Numeric values do not need to be quoted, but when quoted, you might be querying . . . where `UserId`='' . . . which will not error. It will also return no results if all fields are populated.

techtheatre




msg:4108699
 2:46 am on Apr 2, 2010 (gmt 0)

Unfortunately no errors were occouring...and the UserId is actually character based...so these did not resolve it. One thing you said did however solve it (after MUCH beating my head against a wall). I needed to remove the quote marks from around the "date_sub" command.

I started here:
SELECT * FROM timesheet WHERE Timestamp<='DATE_SUB(NOW(), INTERVAL 10 MINUTE)' ORDER BY Timestamp DESC LIMIT 1;

And ended up here:
SELECT * FROM timesheet WHERE Timestamp<=DATE_SUB(NOW(), INTERVAL 10 MINUTE) ORDER BY Timestamp DESC LIMIT 1;


Thanks for the help along the way. So frustrating when things turn out to be so simple...but very happy it now seems to be working.

rocknbil




msg:4109016
 6:17 pm on Apr 2, 2010 (gmt 0)

LOL . . . I can't believe I didn't see the quotes. Ah well. :-P

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved