EhB0QRZS

1SELECT DATE_FORMAT(calendar_date,'%W %D, %M, %Y') AS calendar_date,calendar_entry_title,calendar_entry_teaser
2        FROM calendar_month
3        LEFT JOIN calendar_entry ON calendar_entry.calendar_id = calendar_month.calendar_id
4        ORDER BY calendar_date
5	
6CREATE TABLE IF NOT EXISTS `calendar_entry` (
7  `calendar_entry_id` int(11) NOT NULL AUTO_INCREMENT,
8  `calendar_id` int(11) NOT NULL,
9  `school_id` int(11) NOT NULL,
10  `calendar_entry_title` varchar(250) NOT NULL,
11  `calendar_entry_teaser` varchar(250) NOT NULL,
12  `calendar_entry_text` text NOT NULL,
13  PRIMARY KEY (`calendar_entry_id`)
14) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
15
16--
17-- Dumping data for table `calendar_entry`
18--
19
20INSERT INTO `calendar_entry` (`calendar_entry_id`, `calendar_id`, `school_id`, `calendar_entry_title`, `calendar_entry_teaser`, `calendar_entry_text`) VALUES
21(1, 1, 1, 'School Event 1', 'School event information 1', 'This would be the full body of the text that would show on the full page for this given entry'),
22(2, 1, 1, 'School Event 2', 'School event information 2', 'This would be the full body of the text that would show on the full page for this given entry');
23
24
25
26CREATE TABLE IF NOT EXISTS `calendar_month` (
27  `calendar_id` int(11) NOT NULL AUTO_INCREMENT,
28  `school_id` int(11) NOT NULL,
29  `calendar_date` date NOT NULL,
30  PRIMARY KEY (`calendar_id`)
31) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
32
33--
34-- Dumping data for table `calendar_month`
35--
36
37INSERT INTO `calendar_month` (`calendar_id`, `school_id`, `calendar_date`) VALUES
38(1, 1, '2012-08-11'),
39(2, 1, '2012-08-12');
40	
41Saturday 11th, August, 2012     School Event 1  School event information 1
42Saturday 11th, August, 2012     School Event 2  School event information 2
43Sunday 12th, August, 2012   NULL    NULL
44	
45Saturday 11th, August, 2012     School Event 1  School event information 1 School Event 2   School event information 2
46Sunday 12th, August, 2012   NULL    NULL
47	
48SELECT DATE_FORMAT(calendar_date,'%W %D, %M, %Y') AS calendar_date, TMP.var1
49FROM calendar_month
50LEFT JOIN 
51    (SELECT GROUP_CONCAT(calendar_entry_title, ' ',calendar_entry_teaser) AS var1, calendar_id
52         FROM calendar_entry
53     GROUP BY calendar_id) AS TMP ON TMP.calendar_id = calendar_month.calendar_id
54ORDER BY calendar_date
55	
56$query =" SELECT DATE_FORMAT(calendar_date,'%W %D, %M, %Y') AS calendar_date,
57calendar_entry_title,
58calendar_entry_teaser
59        FROM calendar_month
60        LEFT JOIN calendar_entry ON calendar_entry.calendar_id = calendar_month.calendar_id
61        ORDER BY calendar_date, calendar_entry_title";
62
63$result = mysql_query($query);
64$events = array();
65foreach($result as $r){
66     $events[$r['calendar_date']][] = $r;
67}
68echo '<pre>';
69print_r($events);
70echo '</pre>';