Hi, Thursday, March 24, 2005, 6:50:38 PM, you wrote: J> Dear all
J> Please, I realy need your help. I am trying to extract only those records J> that were entered during the past hour from my MySQL database. The J> following SQL statement extracts all the records. As soon as I change the J> INTERVAL to 1 HOUR I see all the records in my database. When I change J> CURDATE() to CURTIME() no records are extracted from the database. The J> cgtimeposted field is a time data type. J> Please advise me as to how I can formulate my SQL statement so that only J> those records entered within the past hour displays on my page. J> Here is my current SQL statement - this statement displays all the records: J> $sql = "Select tblchatglobal.cgid, tblchatglobal.cgdateposted, J> tblchatglobal.cgtimeposted, tblchatglobal.uid, tblchatglobal.cgmsg, J> tblusers.uid, tblusers.uusername from tblchatglobal, tblusers where J> (tblchatglobal.uid = tblusers.uid) and J> (DATE_SUB(CURDATE(),INTERVAL 1 HOUR) J> <= tblchatglobal.cgtimeposted) order by J> tblchatglobal.cgtimeposted desc"; J> Jacques You cannot do this with just a time field as what happens at midnight and now() becomes 00:00:00, you will need to work with a full date_time field. The other problem is CURDATE has no time component, it is just a date so subtracting 1 hour will always return 23:00:00. So change the time field to a timestamp and try ... AND tblchatglobal.cgtimeposted > (DATE_SUB(NOW(),INTERVAL 1 HOUR)) -- regards, Tom -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
