grouping

[dbdummy]

pulling out my hair on this one. i am trying to query to group by three different groups. the main group is "Month Year" then in month year is "City" then under each city are multiple "Courses" ie

 

September 2007

 

city 1

course  |  dates

course  |  dates

course  |  dates

 

city 2

course  |  dates

course  |  dates

 

October 2007

 

city 1

course  |  dates

course  |  dates

course  |  dates

 

city 2

course  |  dates

course  |  dates

 

 

my database table schema

CREATE TABLE `prodates` (
  `date_id` int(2) NOT NULL auto_increment,
  `date_session_id` smallint(10) NOT NULL,
  `session_month_year` varchar(15) NOT NULL,
  `session_dates` varchar(30) NOT NULL,
  `session_active` varchar(3) NOT NULL default 'yes',
  `session_city` varchar(25) NOT NULL,
  `session_course` varchar(6) NOT NULL,
  PRIMARY KEY  (`date_id`)
) ENGINE=innodb;

CREATE TABLE `monthyear` (
  `my_id` tinyint(4) NOT NULL auto_increment,
  `month_year` varchar(20) NOT NULL,
  PRIMARY KEY  (`my_id`)
) ENGINE=innodb;

CREATE TABLE `courses` (
  `course_id` int(5) NOT NULL auto_increment,
  `course_number` varchar(7) NOT NULL,
  `course_name` varchar(60) NOT NULL,
  PRIMARY KEY  (`course_id`)
) ENGINE=innodb;

CREATE TABLE `cities` (
  `city_id` tinyint(2) NOT NULL auto_increment,
  `city_name` varchar(15) NOT NULL,
  PRIMARY KEY  (`city_id`)
) ENGINE=innodb;

 

My queries

 

<?php 
$query_prodates = "SELECT * FROM prodates p
LEFT JOIN cities ci ON p.session_city = ci.city_name
LEFT JOIN courses co ON p.session_course = co.course_number
LEFT JOIN monthyear my ON p.session_month_year = my.month_year
WHERE p.session_month_year = p.session_month_year AND p.date_session_id = p.date_session_id ORDER BY my.my_id";
$proschedule = mysql_query($query_prodates) or die(mysql_error());
$row_proschedule = mysql_fetch_assoc($proschedule);
$totalRows_proschedule = mysql_num_rows($proschedule);
?>

<table width="100%" border="0" align="left" cellpadding="4" cellspacing="0" bordercolor="#666666" class="bodytext">
                                <tr class="bodytextbold"> 
                                  <td colspan="3" class="header"></td>
                                </tr>
                                <tr bgcolor="#CCCCCC">
                                  <td colspan="2" class="proheaders"><?php echo $row_proschedule['session_month_year']; ?></td>
                                </tr>
                                <tr bgcolor="#CCCCCC"> 
                                  <td colspan="2" class="proheaders"><?php echo $row_proschedule['session_city']; ?></td>
                                </tr>
                                <?php do { ?>
                                <tr class="protext"> 
                                  <td><?php echo $row_proschedule['session_course']; ?></td>
                                  <td><?php echo $row_proschedule['session_dates']; ?></td>
                                </tr>
                                <?php } while ($row_proschedule = mysql_fetch_assoc($proschedule)); ?>

                              </table>