CodeIgniter Forums
Matching Dates - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Matching Dates (/showthread.php?tid=53961)



Matching Dates - El Forum - 08-16-2012

[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();
    }



Matching Dates - El Forum - 08-16-2012

[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?


Matching Dates - El Forum - 08-16-2012

[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.


Matching Dates - El Forum - 08-16-2012

[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();
    }



Matching Dates - El Forum - 08-16-2012

[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


Matching Dates - El Forum - 08-17-2012

[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'");