[SOLVED] MySQL help - Date field greater than or equal to today

[tqla]

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?  ???

[lokie538]

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

 

        }

[tqla]

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.

[DarkWater]

Check out the MySQL NOW() function to get the current time.

[corbin]

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();

[Mchl]

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());

 

 

 

 

[tqla]

Yes! Thank you Mchl and Corbin!!!

 

This worked perfectly!

 

SELECT * FROM blah WHERE field3 >= DATE(NOW());

 

:)