[eluser]james182[/eluser]
Could i get some help with matching dates?
i am trying to count matching dates 2012-04-04 to 2012-04-04 17:00:00
helper
Code: if (!function_exists('event_counter'))
{
function event_counter($year, $month, $day)
{
$dated = $year .'-'. $month .'-'. $day;
$date_is = date('Y-m-d', strtotime($dated));
$counted_events = ci()->calendar_m->count_date_events($date_is);
return print_r($counted_events);
}
}
model
Code: function count_date_events($date_is)
{
return $this->db
->select("DATE_FORMAT(`event_date_begin`,'%Y-%m-%d') AS event_date_begin", FALSE)
->where('event_date_begin', $date_is)
->from('eventcal')
->count_all_results();
}
[eluser]Clooner[/eluser]
What is the exact problem, what did you expect to get, what did you get? and how is the date stored in the database?
[eluser]james182[/eluser]
i am trying to get a number count of events on a specific date.
database field name "event_date_begin" and value "2012-08-04 17:00:00" but i am only needing to match against the date part.
DB
Code: INSERT INTO `default_eventcal` (`id_eventcal`, `user_id`, `event_date_begin`, `event_date_end`, `event_title`, `event_content`, `event_repeat`, `event_repeat_prm`, `privacy`)
VALUES
(2, 1, '2012-08-04 10:00:00', '2012-08-04 17:00:00', 'Picnic', 'This will be a great day.', 0, '{\"type\":\"0\",\"time\":\"0\",\"day\":\"0\",\"date\":\"1\"}', 'public'),
(3, 1, '2012-08-04 16:32:00', '2012-08-04 21:00:00', 'Another Event', 'Cool', 0, '{\"type\":\"0\",\"time\":\"0\",\"day\":\"0\",\"date\":\"1\"}', 'public'),
(4, 1, '2012-08-23 13:01:00', NULL, 'Test Event', 'This is a test event for james to play with.', 0, '{\"type\":\"0\",\"time\":\"0\",\"day\":\"0\",\"date\":\"1\"}', 'private'),
(5, 1, '2012-08-16 21:58:00', NULL, 'werwerwer', 'werwer', 0, '{\"type\":\"0\",\"time\":\"0\",\"day\":\"0\",\"date\":\"1\"}', 'private'),
(6, 1, '2012-08-16 22:03:00', NULL, 'test priv', 'asd', 0, '{\"type\":\"0\",\"time\":\"0\",\"day\":\"0\",\"date\":\"1\"}', 'private');
index.php
Code: echo '<span class="count_events">'. event_counter($current_year, $current_month, $day) .'</span>';
helper.php
Code: if (!function_exists('event_counter'))
{
function event_counter($year, $month, $day)
{
$dated = $year .'-'. $month .'-'. $day;
$date_is = date('Y-m-d', strtotime($dated));
$counted_events = ci()->calendar_m->count_date_events($date_is);
return print_r($counted_events);
}
}
calendar_m->count_date_events()
Code: function count_date_events($date_is)
{
return $this->db
->select("DATE_FORMAT(`event_date_begin`,'%Y-%m-%d') AS event_date_begin", FALSE)
->where('event_date_begin', $date_is)
->from('eventcal')
->count_all_results();
}
I get 1 but thats wrong.
[eluser]Sanjay Sarvaiya[/eluser]
in where cause compare date format Y-m-d.
bellowed code is not tested just for your reference.
Code: function count_date_events($date_is)
{
return $this->db
->select("DATE_FORMAT(`event_date_begin`,'%Y-%m-%d') AS event_date_begin", FALSE)
->where(DATE_FORMAT(`event_date_begin`,'%Y-%m-%d'), $date_is)
->from('eventcal')
->count_all_results();
}
[eluser]james182[/eluser]
i get this error
Code: A Database Error Occurred
Error Number: 1054
Unknown column '0' in 'where clause'
SELECT COUNT(*) AS `numrows` FROM (`default_eventcal`) WHERE `0` = '2012-08-04'
Filename: /xxx/calendar_m.php
Line Number: 13
and if i take out the where clause i get the count 5 which is kinda right, there is 5 events but 2 on one date, 2 on another and 1 on another.
so on those dates i should count 2, 2, 1
[eluser]Sanjay Sarvaiya[/eluser]
hay man user this, you have some thing missing.
Code: $this->db->query("SELECT * FROM `default_eventcal` WHERE DATE_FORMAT(date, '%Y-%m-%d') = '2012-08-04'");
|