tqla Posted January 18, 2009 Share Posted January 18, 2009 Hello. I have a date field in my mysql table. The dates in that field are all formatted like this: 2009-01-01 I need to count the rows that meet the criteria in my query below PLUS with a date greater than or equal to today? $sql = "SELECT * FROM table WHERE field1 = '1' AND field2 = '0' AND field3 = '1' AND field3 >= (HERE IS WHERE IN NEED TO SAY GREATER THAN OR EQUAL TO TODAY)"; $result = mysql_query($sql) or die (mysql_error()); $numrows = mysql_num_rows($result); if($numrows >= 1){ //I'll put code here if there are one or more rows that meet criteria } Is this possible? How? ??? Quote Link to comment Share on other sites More sharing options...
lokie538 Posted January 18, 2009 Share Posted January 18, 2009 How about making a variable with the current time and date? $dt2 = date('Y-m-d H:i:s'); $sql = "SELECT * FROM table WHERE field1 = '1' AND field2 = '0' AND field3 = '1' AND field3 >= " . $dt2 . ")"; $result = mysql_query($sql) or die (mysql_error()); $numrows = mysql_num_rows($result); if($numrows >= 1){ //I'll put code here if there are one or more rows that meet criteria } Quote Link to comment Share on other sites More sharing options...
tqla Posted January 18, 2009 Author Share Posted January 18, 2009 Thanks lokie538 but that doesn't work. I think I need to convert field3 and the current date into something else and then do a comparison. Reading up on it now but so far no luck. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted January 18, 2009 Share Posted January 18, 2009 Check out the MySQL NOW() function to get the current time. Quote Link to comment Share on other sites More sharing options...
corbin Posted January 18, 2009 Share Posted January 18, 2009 What is the datatype of field3? If it's a unix timestamp: SELECT * FROM blah WHERE field3 >= UNIX_TIMESTAMP(); If it's datetime or date: SELECT * FROM blah WHERE field3 >= NOW(); Quote Link to comment Share on other sites More sharing options...
Mchl Posted January 18, 2009 Share Posted January 18, 2009 I understand we're after today, not now, so I would try this way actually If it's a unix timestamp: SELECT * FROM blah WHERE field3 >= UNIX_TIMESTAMP(DATE(NOW())); If it's datetime or date: SELECT * FROM blah WHERE field3 >= DATE(NOW()); Quote Link to comment Share on other sites More sharing options...
tqla Posted January 18, 2009 Author Share Posted January 18, 2009 Yes! Thank you Mchl and Corbin!!! This worked perfectly! SELECT * FROM blah WHERE field3 >= DATE(NOW()); :) 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.