Date and mysql query

Hi,

How can I query records for the current month and the following month?

Thanks

I assume that you have variable holding month.

Query:

$month = mysql_real_escape_string($_POST['month']);
$sql = "select * from table where Month(datetime) = '$month'";

datetime is field form database.

Query the ‘date’ field in the table, to get records for the current month and the following month

Thanks

Can you explain more, like are you providing links to users:
This month, Previous month.

Are a drop down for the months?

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_month
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

If you still have trouble, post some code.

so if you were to run the query today (january 30th) you want rows for all of january 2010 and all of february 2010 also, yes?

your code should look like this –

WHERE datecol >= '2010-01-01'
  AND datecol  < '2010-01-03'

using this approach will allow an index on datecol to be utilized – most other approaches, such as using MONTH(datecol), will result in a table scan, which is a lot slower

My current query is life the following to select records for the current month. But I want the current and the following month… But without changing the query each month…

SELECT * FROM events WHERE YEAR(eventdate) = YEAR(CURDATE()) AND MONTH(eventdate) = MONTH(CURDATE()) ORDER BY eventdate ASC

Thanks

to find the first day of the current month, subtract from the current date a number of days which is one less than the number of the current day in the month

so for example if today is the 14th, you would subtract 13 days to get you the first day of the month

this is given by the formula CURRENT_DATE - INTERVAL DAY(CURRENT_DATE) - 1 DAY

then to get the first day of the month of the second following month, just add two months to the date of the first formula

so, using the WHERE conditions i descripbed before, you would do this –


WHERE datecol >= CURRENT_DATE - INTERVAL DAY(CURRENT_DATE) - 1 DAY
  AND datecol  < CURRENT_DATE - INTERVAL DAY(CURRENT_DATE) - 1 DAY
                              + INTERVAL 2 MONTH

of course, you could also do this type of calculation in php, so that the dates are actually plugged in as constant values

you wouldn’t be changing the calculation each month in php any more than you would need to by doing it in sql