[eluser]Jay Logan[/eluser]
I'm trying to put together a page that sorts "meets" and groups them together by month & year. IE:
Jan 2009
- Meet 1
- Meet 2
- Meet 3
Feb 2009
- Meet 4
- Meet 5
March 2009
-Meet 6
Etc. etc.
So far, I have been able to produce a page showing all meets ordered by date (from MySQL). But I'm not sure how to tackle this issue. Do I do it in the model? I'm assuming I need to make a new key for each group. Still new to programming. Any help would be appreciated. Here is my current code.
Code: function get_all_meets()
{
$this->db->select();
$this->db->from('meets');
$this->db->where('hide', '0');
$this->db->orderby('date');
$query = $this->db->get();
$result = $query->result_array();
$i = 0;
foreach ($result as $meet) {
$this->db->select();
$this->db->from('meet_events');
$this->db->where('meet_id', $meet['id']);
$query = $this->db->get();
$events = $query->result_array();
$result[$i++]['event_count'] = count($events);
}
return $result;
}
[eluser]pistolPete[/eluser]
Could you post your database layout (with some sample data)?
[eluser]Jay Logan[/eluser]
Here are the fields I use. The 'date' field is what I want to use to group the meets by.
id varchar(32)
name varchar(255)
date datetime
venue varchar(255)
address varchar(255)
city varchar(255)
state varchar(255)
zipcode varchar(255)
website varchar(255)
reg_start datetime
reg_end datetime
info_page_content longtext
password varchar(255)
director_name varchar(255)
director_email varchar(255)
director_phone varchar(255)
hide tinyint(4)
host_id
[eluser]Jay Logan[/eluser]
Better yet, here is a dump.
Code: CREATE TABLE IF NOT EXISTS `meets` (
`id` varchar(32) NOT NULL default '0',
`name` varchar(255) NOT NULL default '',
`date` datetime default NULL,
`venue` varchar(255) NOT NULL default '',
`address` varchar(255) NOT NULL default '',
`city` varchar(255) NOT NULL default '',
`state` varchar(255) NOT NULL default '',
`zipcode` varchar(255) NOT NULL default '',
`website` varchar(255) NOT NULL default '',
`reg_start` datetime default NULL,
`reg_end` datetime default NULL,
`info_page_content` longtext,
`password` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
`director_name` varchar(255) default NULL,
`director_email` varchar(255) default NULL,
`director_phone` varchar(255) default NULL,
`hide` tinyint(4) NOT NULL default '0',
`host_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `meets`
--
INSERT INTO `meets` (`id`, `name`, `date`, `venue`, `address`, `city`, `state`, `zipcode`, `website`, `reg_start`, `reg_end`, `info_page_content`, `password`, `director_name`, `director_email`, `director_phone`, `hide`, `host_id`) VALUES
('200001', 'Hornet 8 Way Invitational', '2009-04-04 09:00:00', 'Roswell High School', '', 'Roswell', 'Georgia', '', '', '2008-12-12 17:53:00', '2009-04-01 23:59:00', '<p><strong>Contact Information: </strong></p><p>Andy Schultz </p><p>[email protected] </p><p>Limit 3 Athletes per events and 2 teams per relay event.</p>', 'noahdog15', 'Schultz, Andy', '[email protected]', '770-552-4500', 0, 0);
INSERT INTO `meets` (`id`, `name`, `date`, `venue`, `address`, `city`, `state`, `zipcode`, `website`, `reg_start`, `reg_end`, `info_page_content`, `password`, `director_name`, `director_email`, `director_phone`, `hide`, `host_id`) VALUES
('154846538', 'Coaches Invitational', '2008-03-14 17:00:00', 'Griffin Stadium, Georgia Tech, Atlanta, GA', '', '', '', '', '', '2008-02-04 08:00:00', '2008-03-09 23:59:00', 'ENTRIES WILL CLOSE ON 3-8-08 @ 8PM. \nNotification of selection will be posted via www.gatfxc.com and on www.GASports.com.', '', NULL, NULL, NULL, 1, 0);
INSERT INTO `meets` (`id`, `name`, `date`, `venue`, `address`, `city`, `state`, `zipcode`, `website`, `reg_start`, `reg_end`, `info_page_content`, `password`, `director_name`, `director_email`, `director_phone`, `hide`, `host_id`) VALUES
('33c36fb44790b8630049b852ce308', 'CCYTL - Meet B', '2009-03-07 08:00:00', 'Agnes Scott College', '141 E. College Ave.', 'Decatur', 'GA', '30030', 'http://www.ccytl.homestead.com', '2009-02-28 22:30:00', '2009-03-04 23:30:00', '<p>Due to the venue owners not wanting the in-field to be used, this Meet will have all Running Events Only. Each athlete can contest 3 running events.</p><p>The 200M race has been added to the list of events to be contested.</p>', '', 'Collins, Jacqui', '[email protected]', '770-528-0306', 1, 0);
INSERT INTO `meets` (`id`, `name`, `date`, `venue`, `address`, `city`, `state`, `zipcode`, `website`, `reg_start`, `reg_end`, `info_page_content`, `password`, `director_name`, `director_email`, `director_phone`, `hide`, `host_id`) VALUES
('522a1f6b4790bb076f9ab12a9adc', 'SC - 2009 SCISA State Track Meet', '2009-05-02 08:00:00', 'Heathwood Hall', '', 'Columbia', 'SC', '', '', '2009-04-01 08:00:00', '2009-04-28 23:59:00', '', '', '', '', '', 0, 0);
[eluser]pistolPete[/eluser]
And the table meet_events?
[eluser]Jay Logan[/eluser]
Code: CREATE TABLE IF NOT EXISTS `meet_events` (
`id` varchar(32) NOT NULL default '0',
`meet_id` varchar(32) NOT NULL default '0',
`event_id` varchar(32) NOT NULL default '0',
`gender` char(1) NOT NULL default '',
`max_athletes` smallint(5) unsigned NOT NULL default '0',
`max_signups` smallint(5) unsigned NOT NULL default '0',
`lynxpad_id` varchar(32) NOT NULL,
`measure` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `event_id` (`event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `meet_events`
--
INSERT INTO `meet_events` (`id`, `meet_id`, `event_id`, `gender`, `max_athletes`, `max_signups`, `lynxpad_id`, `measure`) VALUES
('78a0c32947916fd27afb444f587c0', '154846538', '2', 'F', 0, 0, '', 'M'),
('4ae71fc247916fc724af094fbfc5d', '154846538', '18', 'M', 0, 0, '', 'M'),
('7439fd6447916fae918176280a2bc', '154846538', '6', 'F', 0, 0, '', 'M'),
('4abfc1047916fa39456e3412d22d', '154846538', '6', 'M', 0, 0, '', 'M'),
('7fec1c3a47916f8a692d7436603a0', '154846538', '5', 'F', 0, 0, '', 'M'),
('257b1e247916f8340d332340f36', '154846538', '5', 'M', 0, 0, '', 'M'),
('3961fdfa47916fe2d7df415dc5d17', '154846538', '1', 'M', 0, 0, '', 'M'),
('5259fd6647916fee9b8e2712e3fac', '154846538', '1', 'F', 0, 0, '', 'M'),
('5912074c4791700332487642bc846', '154846538', '12', 'M', 0, 0, '', 'M'),
('73aa44b2479170102e02883ed4403', '154846538', '12', 'F', 0, 0, '', 'M'),
('44829171479170503ce9b511f724f', '154846538', '6a7e968b45743122da95d859c36d8', 'M', 0, 0, '', 'M');
INSERT INTO `meet_events` (`id`, `meet_id`, `event_id`, `gender`, `max_athletes`, `max_signups`, `lynxpad_id`, `measure`) VALUES
('6e7a1db747917064a244082908d1b', '154846538', '6a7e968b45743122da95d859c36d8', 'F', 0, 0, '', 'M'),
('8b7ad7b4791711dd7df385637bf6', '154846538', '14', 'M', 0, 0, '', 'M'),
('20e972cb47917129eb5ec11d96737', '154846538', '14', 'F', 0, 0, '', 'M'),
('211c1254791713ab32b4075cfa0', '154846538', '16', 'M', 0, 0, '', 'M'),
('68a371574791714c95f2c15c3caaa', '154846538', '17', 'F', 0, 0, '', 'M'),
('5e4cbcd24791715d58fb9251b65d9', '154846538', '15', 'M', 0, 0, '', 'M'),
('458e7d87479171692179b89056fe', '154846538', '15', 'F', 0, 0, '', 'M'),
('626a0ec9479176dc5741178f798f', '154846538', '2bd115e44791748867b4c734c1832', 'M', 0, 0, '', 'M'),
('2b542a7479176ec3ce5102801992', '154846538', '2bd115e44791748867b4c734c1832', 'F', 0, 0, '', 'M'),
('2e03c53e47917765360ae62c9fe18', '154846538', '17', 'M', 0, 0, '', 'M'),
('3ea93e3647917785ce76033bb844d', '154846538', '13', 'M', 0, 0, '', 'M');
INSERT INTO `meet_events` (`id`, `meet_id`, `event_id`, `gender`, `max_athletes`, `max_signups`, `lynxpad_id`, `measure`) VALUES
('2057de014791779154844352be87a', '154846538', '13', 'F', 0, 0, '', 'M'),
('77abad19479177cc79e3b913fd528', '154846538', '16', 'F', 0, 0, '', 'M'),
('f11757f479177e81286c81ba01c5', '154846538', '459f0b4845743170c83d182d64abd', 'M', 0, 0, '', 'M'),
('3105f85f479177f4a9297921a3738', '154846538', '459f0b4845743170c83d182d64abd', 'F', 0, 0, '', 'M'),
('1a649d4447d56d4276dc63419cbc7', '706de28d47d56ce9b17c205a6ea0e', '7', 'M', 2, 0, '', 'M'),
('79ec598947d565c79513f02299170', 'bab96e947d55f3af3a9463590d1f', '7', 'F', 2, 0, '', 'M'),
('1341067a47d56d37beb2047714e5', '706de28d47d56ce9b17c205a6ea0e', '10', 'M', 2, 0, '', 'M'),
('420a23bb47d56d039166903d63b40', '706de28d47d56ce9b17c205a6ea0e', '18', 'M', 2, 0, '', 'M'),
('417aad4947d5682be105353b724c1', 'bab96e947d55f3af3a9463590d1f', '1', 'F', 2, 0, '', 'M');
[eluser]jedd[/eluser]
Hi J-Slim. Might not seem like a particularly helpful suggestion at this point - but I really believe your life would be a lot easier if you cleaned up your schema. There's some good oil on normalisation out on the interwebs. You don't have to go overboard with this stuff, but just working through the first 3 NF's often gives a lot of useful insight about your own data.
Some examples - an 'event' would normally imply a date. venue information should be in its own table. ditto director. event_id should probably be an FK or an index rather than a key proper, esp. given the 1:n relationship (by my reading).
I think a lot of coding frustration and problems end up being exacerbated by the underlying db design, so some time spent now before you've cut much code will probably save a lot more later.
[eluser]xwero[/eluser]
Code: function get_meets_year($year)
{
$year_meets = array();
for($i=1;$i<13;$i++) // the number of months is not going to change
{
$year_meets[$i] = $this->query('SELECT field FROM table WHERE MONTH(meet_date) = ?',array($i))->result();
}
return $year_meets;
}
i used the query method because the sql statement has a mysql function.
[eluser]Jay Logan[/eluser]
I think I'm getting there with your code xwero but I think your code only works for the 12 months in one year. What I'd like to do is display all open meets available on the site and that sometimes includes meets occurring in the following year. Like November 2009, December 2009, January 2010, February 2010, etc.
[eluser]xwero[/eluser]
I think a simple query will do in that case
[quote]SELECT meet_date FROM table WHERE open=1 AND meet_date > CUR_DATE() ORDER BY meet_date[/code]
When you loop the results you check the year and month for changes to make your groups on the page.
|
