events "calender" for 12 month rolling cycle

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mcsleazycrisps
Forum Newbie
Posts: 6
Joined: Mon Aug 25, 2003 7:12 am
Location: UK

events "calender" for 12 month rolling cycle

Post by mcsleazycrisps »

I'm working on a system to display a band's gigs list for the next 12 months (i.e. not from Jan - Dec. If it's August, Show Aug - July Next Year).
It needs to show
Month
Date - Gig Description
Date - Gig Description
Date - Gig Description

Next Month
Date - Gig Description
Date - Gig Description
Date - Gig Description

I'm storing the date in my MySQL DB as yyyymmdd format.

Currently I've got it running 12 MySQL queries, like this:

Code: Select all

<?php
 $curmonth = date("n");
 $endmonth = date("n")+12;  // SHOWS THE NEXT 12 MONTHS;

 for($i = $curmonth; $i < $endmonth; $i++){
 $tempmonth = $i;
 if ($tempmonth > 12){$tempmonth = $tempmonth-12;}; // IF PAST DEC, GO BACK 12 MONTHS

if (!$curyear){$curyear=date("Y");};

 // ASSEMBLE SQL QUERY

 $pquery = "select * from gigs where ";

//THIS PULLS GIGS THAT ARE ONLY IN THE CURRENT MONTH RANGE

 if ($tempmonth == 12){
  $pquery .= 'gigdate >= "'.$curyear.(sprintf('%02d',$tempmonth)).'01" AND gigdate < "'.($curyear+1).'0101"';
 }else{
  $pquery .= 'gigdate >= "'.$curyear.(sprintf('%02d',$tempmonth)).'01" AND gigdate < "'.$curyear.(sprintf('%02d',$tempmonth+1)).'01"';
 }

 $pquery .= " ORDER BY gigdate ASC";

 // Send the query to MySQL for execution

if ($numrows > 0){
 // DISPLAY EACH GIG FOR THIS MONTH
} 
 
 if ($tempmonth > 11){  //GO TO NEXT YEAR IF FINISHED DECEMBER
  $curyear++;  //START NEXT YEAR
 }

}
?>
This seems a bit inefficient to me, is there any easier way of doing this? I looked at using MySQL's date_add() and date_sub() but seemed more trouble than it's worth :?
Post Reply