选择今年的日期和按月分组

Question

I'm working on an events page where users can create events and their followers can view the event on their events page.

What I would like to do is select all the events from the table where the event date is within this year (2011), so if the date is 2011-03-30 it will display in the March header, but if it's 2012-03-30 it wont display at all until next year.

So overall, I would like all events to display under headers, e.g. January, February, March.

My table structure is thus:

id                             int(11)
event_creator_user_id          int(11)
event_creator_user_username    varchar(255)
event_creator_user_first_name  varchar(255)
event_creator_user_last_name   varchar(255)
event_name                     varchar(255)
event_date                     date
event_time                     time
event_brief_location           varchar(255)
event_location_street          varchar(255)
event_location_town            varchar(255)
event_location_post_code       varchar(255)
event_description              text
event_create_date              datetime
type                           enum('a','b')

Answer 1

I think you are over complicating the problem. This can be solved by simply getting all the events in the current year using MySQLs DATE_FORMAT function and ordering the events by date.

Then in PHP you can simply loop over them and when the month changes print out a heading. The code below should make this clearer.

In PHP:

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("mydbname");

$SQL = "
    SELECT *,
           UNIX_TIMESTAMP(`event_date`) AS event_date_timestamp
    FROM events
    WHERE DATE_FORMAT(event_date, '%Y') = 2011
      AND event_date > NOW()
    ORDER BY event_date ASC";

$result = mysql_query($SQL);

$current_month = '';
while ($event = mysql_fetch_assoc($result)) {
    $month = date('m', $event['event_date_timestamp']);
    if($current_month != $month) {
        $current_month = $month;
        echo '<h1>' . $current_month . '</h1>';
    }
    echo '<p>' . $event['event_name'] . '</p>';
}