[SOLVED] Monthly Event list woes :(

[dmschenk]

I'm trying desperately to build a Monthly Event List but I am only losing hair. Basically what I want is to list out the events for the month under a single header.  If anyone thinks I'm going about this in the wrong manor please lemme know.

 

Example:

May

Event 1 - May 01, 2007

Event 2 - May 01, 2007

Event 3 - May 15, 2007

 

June

Event 4 - June 01, 2007

Event 5 - June 10, 2007

Event 6 - June 12, 2007

 

one problem I am having is to list events that have the same date like events 1 & 2

 

The other Problem is that if I use "GROUP BY MONTH" in my sql statement then I get the separated months but only one event per month

 

I'm trying to replace this static html:

http://internationalsoaringeaglesministries.org/calendar.php

 

 

mysql_select_db($database_ISEMCal, $ISEMCal);
$query_isemCal = "SELECT calendar.EventDate, calendar.EventDesc, calendar.EventLocation, calendar.`Date` 
FROM calendar 
GROUP BY MONTH (calendar.Date)";
$isemCal = mysql_query($query_isemCal, $ISEMCal) or die(mysql_error());
$newMonth = mysql_fetch_assoc($isemCal);
$totalRows_isemCal = mysql_num_rows($isemCal);



    $oldMonth = "";   //initialize a variable to hold the current month
    echo '<table width="400" border="2" align="center" cellpadding="10" cellspacing="0">'
    ;
    while ($newMonth = mysql_fetch_assoc($isemCal))
    {

      if ($oldMonth != date('F - Y', $newMonth['Date']))
      {

        echo '<tr bgcolor="#E4C6E2">
              <td colspan="2" class="monthYear"><div align="center">';

        echo date('F - Y', strtotime($newMonth['Date']) ) ;

        echo '</div></td></tr>';

        $oldMonth = date('F - Y', $newMonth['Date']);
      }
      echo '<tr valign="top">
            <td width="30%"><p>'.$newMonth['EventDate'].'</p></td>
            <td width="70%">'.$newMonth['EventDesc'].'<br>'.
            $newMonth['EventLocation'].'</td>
            </tr>';
    }
    echo '</table>
            <br>
            </body>
            </html>';
mysql_free_result($isemCal);

 

 

Here is a quick test db with 4 entries :

 

-- phpMyAdmin SQL Dump
-- version 2.10.0.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: May 19, 2007 at 01:13 AM
-- Server version: 5.0.27
-- PHP Version: 5.2.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `isem`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `calendar`
-- 

CREATE TABLE `calendar` (
  `CalendarID` int(11) NOT NULL auto_increment,
  `Date` date NOT NULL,
  `EventDate` varchar(15) NOT NULL default '',
  `EventDesc` text NOT NULL,
  `EventLocation` varchar(50) NOT NULL default '',
  `OnLine` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`CalendarID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='ISEM Calendar of Events' AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `calendar`
-- 

INSERT INTO `calendar` (`CalendarID`, `Date`, `EventDate`, `EventDesc`, `EventLocation`, `OnLine`) VALUES 
(1, '2007-06-13', '1', 'Event 1', 'Roseville, MI', 1),
(2, '2007-06-13', '1', 'Event 2', 'Ypsilanti, MI', 1),
(3, '2007-06-29', '26 - 28', 'Event 3', 'Selfridge Chapel, MI', 1),
(4, '2007-08-15', '13 - 18', 'Event 4', 'Selfridge Chapel, MI', 1);

 

Any help is appreciated

 

[Barand]

try

<?php

$sql = "SELECT MONTHNAME(Date), EventDesc, Date FROM calendar ORDER BY Date";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
$prevm = $prevd = '';
echo "<table border='0' width='300'>\n" ;
while (list($mon, $desc, $dt) = mysql_fetch_row($res))
{
    $dt = date('F d, Y', strtotime($dt));
    if ($prevm != $mon)
    {
        echo "<tr><td colspan='2' style='font-weight: 600'><br>$mon</td></tr>\n";
        $prevm = $mon;
    }
    if ($prevd != $dt)
    {
        echo "<tr><td><br>$desc</td><td><br>$dt</td></tr>\n";
        $prevd = $dt;
    }
    else
    {
        echo "<tr><td>$desc</td><td> </td></tr>\n";
    }
}
echo "</table>\n";  
?>

[dmschenk]

Awesome!  this works great!  I've been asking this question on various forums for a while now so far this is the only working code I've seen!

Thanks

Dave