PDA

View Full Version : mySQL code for showing today's records



kwaka
13-09-2006, 12:22 PM
Hi, Probably a really easy question to one who deals with mySQL and PHP, but as I never seem to find the time to RTFM I will ask here.
I have mySQL ver 4.0.20a and PHP ver 1.52 running on a win2003 server box, have built the pages etc using Dreamweaver MX, and all works as I want. Have been asked to just show the records inserted on that day. I am using the UNIX_TIMESTAMP(time) AS time function to allow the dreamweaver extensions to show date/time in a recognisable format, was wondering if I could add the WHERE time=day() argument to get the current days records to show. Any help would be appreciated.

TGoddard
13-09-2006, 01:58 PM
To show last 24 hours:

... WHERE date_column > (CURTIME() - INTERVAL 1 DAY);

To show current day:

... WHERE DATE(date_column) = CURDATE();

kwaka
13-09-2006, 05:20 PM
Many Thanks for the reply, am getting SQL errors with the WHERE statements, this is the last SQL statement I tried.
SELECT pupil, reason, UNIX_TIMESTAMP(time) AS time
FROM room1
WHERE DATE(time) = CURDATE();
ORDER BY pupil ASC
Have a feeling that as its not native unix timestamp it may be causing a problem with the earlier statement to change from the mySQL timestamp to the unix one. or :blush: I have lost the plot with the SQL syntax and it shouldnt be DATE. I never have much fun reading thru online manuals.
Thanks again.

TGoddard
14-09-2006, 12:07 AM
Where are you using UNIX time stamps? Aren't you using one of the Date/Time data types? It would be horribly inefficient to store these times as a string then compare them too often.