[Resolved] Events match

zzz

I have events recorder in db with the following type date format: 2005-05-31.

How do I select the ones that are of this month?

Matt_Kindig

Here's the SQL

SELECT * FROM events WHERE MONTH(date)=MONTH(NOW())

zzz

You rock!

What is it's the first of the month and i would like to list events for the previous months? Would I just run a check for this months and if there's none, go to previous?

Matt_Kindig

When you are talking about more complicated date queries, it might be easier to simply use timestamps for everything. Here's some info to get you started.
1. In PHP, strtotime to convert date string to timestamp, mktime to create timestamps from dates, date to format dates, and time to get the present timestamp.
2. In MySQL, the date and time functions to perform date calculations. The UNIX_TIMESTAMP() function may be useful to convert your dates to timestamps.
So a strategy here would be:
1. In PHP, find current day of month. (time() function)
2. If day of month == 1, create timestamp for previous month. (mktime() function)
3. Find month number for previous month (date() function)
4. Run SQL query similiar to what you did before.

zzz

Thanks.

I am not sure how do I create timestamp for previous month. (mktime() function) ...

I found today's date and can do conditional statement if, say only a week elapsed, check previous month. I can find today's month and assume that previous is -1. Isn't it easier that way?

Sorry for all these extra questions.

Matt_Kindig

This gets a little more complicated but very do-able:

<?php
// gets current month
$currMonth=date('n',time());
// makes timestamp of previous week
$prevWeek=strtotime("-1 week");
// gets Month of previous week
$monthPrevWeek=date('n',$prevWeek);

$checkMonth=$currMonth;

// if this month is greater than last week's month number (i.e. last week was a different month), use last week's month to check events
if ($currMonth > $monthPrevWeek) {   

     $checkMonth=$monthPrevWeek;
}

// form query
$query="SELECT * FROM events WHERE MONTH(date)=$checkMonth";

Use of -1 for previous month is also possible but I don't have time to work through the logic right now.

zzz

Despite not having time, you just saved me hours!

Matt_Kindig

One cavaet: This assumes that all events in the database are from the same year, as the script cannot distinguish between May 2005 and May 2004. To add this functionality would almost certainly involve UNIX_TIMESTAMP() commands (and most likely BETWEEN statements in MySQL). I'd recommend reading up on them if you need to consider multiple years.

zzz

Oops, I need to figure out that one before the next year 😉

I found this in the manual. It does have Y. Couldn't it be done somehow similarly?

$lastmonth = mktime(0, 0, 0, date("m")-1, date("d"), date("Y"));