covert number of days into weeks with respect to number of days in php

Question

i've a filter which gets the data from my DB on date filter base which finds the data between two dates. e.g from = 2013-12-15 , to = 2013-12-20 etc. and return me resutl with date on which my required data is found.

now i want if user selects the dates which are greater than 20 days like 25 days selected from date filter or more than 1 month which could be lead upto 5 months then it should be converted into WEEKS format and it give me back data on week base and show me data against week number.

<?php $date_from = 2012-12-01; $to_date = 2013-12-31;  ?>

like in above i have one month or 4 week . now i want to show data on week base as i have more than 20 day difference between above two dates.

i am applying this in Google area chart. now i want to show data in weeks base rather on single date base.

i need solution in php, mysql.

my code

this is my controller function which gets the post date values

public function get_sales_graph(){

      if($this->input->post()){

        $type = $this->input->post('type');  
        $get_date = str_replace("/", "-", $this->input->post('chosen_from_date'));
        $get_date = DateTime::createFromFormat('m-d-Y', $get_date);
        $from_date = $get_date->format('Y-m-d');

        $get_to_date = str_replace("/", "-", $this->input->post('chosen_to_date'));
        $get_to_date = DateTime::createFromFormat('m-d-Y', $get_to_date);
        $to_date = $get_to_date->format('Y-m-d');

        $date1 = new DateTime($from_date);
        $date2 = new DateTime($to_date);
        $interval = $date1->diff($date2);
        $day_count = $interval->format('%a');
      }

        $user_id = $this->user->id;
        $rows['graph'] = $this->events_model->my_promo_graph_model($from_date, $to_date, $user_id);            
        $result = count($rows['graph']);
        $data['data'] = array();
        if($result > 0){

            array_push($data['data'], array('DATE', 'Sales'));
               foreach($rows['graph'] as $row){
                    array_push($data['data'], array($row['date_display'], (int)$row['ticketss_sold_on_date'])); 
               } 

        }
        else{
            $i = 1;
            $day_count = $day_count == 0 ? 1 : $day_count;
            array_push($data['data'], array('DATE', 'Sales'));
            while($i <= $day_count){
                array_push($data['data'], array(date('Y-m-'.$i), 0 ));
                $i++;
            }

        }


       echo json_encode($data);          
  }

and here is my model

public function my_promo_graph_model($from_date, $to_date, $user_id){

        $this->db->select('DATE(tickets_sold.date) AS date_display,
                          SUM(tickets_sold.quantity) AS ticketss_sold_on_date,
                          SUM(tickets_sold.quantity*tickets_sold.price) AS money_earned');

        $this->db->join('tickets_sold', 'tickets_sold.event_date_id = my_promos.event_id');
        $this->db->where('my_promos.user_id', $user_id);
        $this->db->where('DATE(tickets_sold.date) >=', $from_date);
        $this->db->where('DATE(tickets_sold.date) <=', $to_date);
        $this->db->group_by('date_display');
        $query = $this->db->get('my_promos');
        return $query->result_array();
    }

What does your codeigniter and google-visualisation tags have to do with this question? — crafter, Jan 02 '14 at 07:26
i've mentioned that these are controlllers and model files and are of CI, and i've also metioned that i am applying this to draw a google area chart ghraph. @crafter — saad, Jan 02 '14 at 07:35
@saad, I saw that, but your problem has nothing to do with these 2 tags really. Someone who sees 'google-visualisation' may come trying to help or get help and see that it is really a PHP/MySQL language issue. Your call, I just saying that these 2 are not as relevant as the 'php' and 'mysql' tag, in the interest of keeping the site tags clean. Not a big issue really. I'm just saying ;) — crafter, Jan 02 '14 at 08:17
Maybe this is what you are looking for : http://stackoverflow.com/questions/9567673/get-week-number-in-the-year-from-a-date-php — crafter, Jan 02 '14 at 08:20
If you are looking to group data by week, this should help: http://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql — asgallant, Jan 02 '14 at 16:06

score 0 · Answer 1 · answered Jan 30 '14 at 10:02

Check the date range in PHP before your query, then ask MySQL to group by week instead of day, as the links in comments suggest. At the moment you're asking for:

$this->db->group_by('date_display');

Try:

$this->db->group_by('YEARWEEK(date_display, 3)');

Which week mode you want might be different to this. Using YEARWEEK rather than just WEEK means that you can sort by it safely.

covert number of days into weeks with respect to number of days in php

1 Answers1