newbtophp Posted August 5, 2010 Share Posted August 5, 2010 Hi, Im currently using the following code, to list 'Todays 5', however can the following be improved im uneasy with using php for the date comparison - perhaps have it within the sql? DB Structure: I have a column named 'flash_date' (and the type is: varchar(32)) which contains a timestamp generated by time() $yesterday = strtotime('-1 day'); $time = time(); $result = mysql_query("SELECT * FROM site_flash WHERE flash_date > '{$yesterday}' AND flash_date < '{$time}' DESC LIMIT 5"); Quote Link to comment Share on other sites More sharing options...
JonnoTheDev Posted August 5, 2010 Share Posted August 5, 2010 DB Structure: I have a column named 'flash_date' (and the type is: varchar(32)) which contains a timestamp generated by time() Why 32 characters? A timestamp is an integer with a length of 10 (INT 10). Convert this. SQL SELECT * FROM site_flash WHERE flash_date > UNIX_TIMESTAMP(CURDATE() - 1) AND flash_date < UNIX_TIMESTAMP() DESC LIMIT 5 Quote Link to comment Share on other sites More sharing options...
newbtophp Posted August 5, 2010 Author Share Posted August 5, 2010 DB Structure: I have a column named 'flash_date' (and the type is: varchar(32)) which contains a timestamp generated by time() Why 32 characters? A timestamp is an integer with a length of 10 (INT 10). Convert this. SQL SELECT * FROM site_flash WHERE flash_date > UNIX_TIMESTAMP(CURDATE() - 1) AND flash_date < UNIX_TIMESTAMP() DESC LIMIT 5 I don't know why I did varchar(32), at that time I was a total newby, and every tutorial I read always seemed to mention varchar(32), (so I just used that, never really knowing what it is) - now I know it means 32 chars I changed it to INT(10), cheers for that query I'll use it, but just a thought would this work also? SELECT * FROM site_flash WHERE flash_date > (now() - INTERVAL 1 day) DESC LIMIT 5 Quote Link to comment Share on other sites More sharing options...
JonnoTheDev Posted August 5, 2010 Share Posted August 5, 2010 No because NOW() returns the current date & time, not a unix timestamp Quote Link to comment Share on other sites More sharing options...
newbtophp Posted August 5, 2010 Author Share Posted August 5, 2010 Ok cheers neil /Solved Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.