CODEWITHSUNDEEP
phpsqldate
Frank
Frank

Reputation: 11

Select where date is in this year and group by month

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')

Upvotes: 0

Views: 2715

Answers (3)

Treffynnon
Treffynnon

Reputation: 21553

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>';
}

Upvotes: 2

shimofuri
shimofuri

Reputation: 691

You can get the year part of the date using a function. Assuming it's MySQL:

SELECT 
   *,
   MONTH(event_date) AS 'month'
FROM events
WHERE YEAR(event_date) = YEAR(CURDATE())
ORDER BY month ASC

Upvotes: 2

Tim
Tim

Reputation: 14446

In my databases, I store dates as int as the unix timestamp, number of seconds since jan 1 1970. This way, if I want to select a range (such a just the month of March) i can use mktime:

$query = mysql_query("SELECT * FROM `events` WHERE `date` > ".mktime(0,0,0,3,1,2011). " AND `date` < ".mktime(0,0,0,4,1,2011));

You could loop through months this way:

for($i=1; $i<=12; $i++){
   $query = mysql_query("SELECT * FROM `events` WHERE `date` > ".mktime(0,0,0,$i,1,2011). " AND `date` < ".mktime(0,0,0,$i+1,1,2011));
   $monthName = date("F",mktime(0,0,0,$i,1,2011)); // full name of the month; use "M" for short-name
   // ... your code for printing the events here
}

I would help with the date data type but I haven't used it.

Upvotes: 1

Related Questions