Display data week by week

On my model function it gets the total number of users that have joined up for this day in the week and then sets it on chart.

Sun, Mon, Tue, Wed, Thu, Fri, Sat



On my database table I have two users that have joined up on the 19th of aug and one on 21

My model function currently prints code like.

Code:

{m: 'Sun', a: 0},

{m: 'Mon', a: 0},

{m: 'Tue', a: 1}, // 2016 - 08 - 21

{m: 'Wed', a: 0},

{m: 'Thu', a: 0},

{m: 'Fri', a: 2}, // 2016 - 08 - 19

{m: 'Sat', a: 0},

Because my weeks start on each Sunday it should only display something like

Code:

{m: 'Sun', a: 0},

{m: 'Mon', a: 0},

{m: 'Tue', a: 1}, // 2016 - 08 - 21

{m: 'Wed', a: 0},

{m: 'Thu', a: 0},

{m: 'Fri', a: 0},

{m: 'Sat', a: 0},

Question: On my model what would be best solution to be able to get and display correct result

PHP Code:

public function getUserTotalByWeek() {
    $date_start = strtotime('-' . date('w') . ' days');

    $user_data = array();

    $this->db->select('*, COUNT(*) AS total');
    $this->db->from($this->db->dbprefix . 'user');
    $this->db->where('DATE(date_reg) >=', $date_start);
    $this->db->group_by('DAYNAME(date_reg)');
    $query = $this->db->get();

    for ($i = 0; $i < 7; $i++) {
        $date = date('Y-m-d', $date_start + ($i * 86400));
        $user_data[date('w', strtotime($date))] = array(
            'day'   => date('D', strtotime($date)),
            'total' => 0
        );
    }

    foreach ($query->result_array() as $result) {
        $user_data[date('w', strtotime($result['date_reg']))] = array(
            'day'   => date('D', strtotime($result['date_reg'])),
            'total' => $result['total']
        );
    }

    return $user_data;
} 


Controller index function

PHP Code:

public function index() {

    $data['results'] = array();

    if ($this->input->post('range')) {
        $range = $this->input->post('range');
    } else {
        $range = 'week';
    }

    switch ($range) {
        case 'week':
            $results = $this->getUserTotalByWeek();

            foreach ($results as $result) {
                $data['results'][] = array(
                    'data' => "\n" . "{m: " . "'" . $result['day'] . "'" . ', ' . 'a: '. $result['total'] . '},' . "\n"
                );
            }

            break;
        case 'month':
            # code...
            break;    
    }

    return $this->load->view('template/dashboard/chart_view', $data);
} 


Thank you for your time

Attached Files

  Chart.php (Size: 1.44 KB / Downloads: 74)

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!

If I understand you correctly, you only want to display data for the one week, so you only need to select data for that week.

You can work out the midnight Sunday and midnight following Saturday time stamps for the required week and select your data like this:

PHP Code:

$this->db->where('DATE(date_reg) >=', $week_start);
$this->db->where('DATE(date_reg) <=', $week_end); 


Alternatively, in your for loop, you could add an if statement to only add data for the required week in a similar manner.

Or have I got the wrong end of the stick?

Hope that helps,

Paul.

I thought about what you said just would like to know if done this correct and if any improvement required.

PHP Code:

public function getUserTotalByWeek() {
$date_start = strtotime('last Sunday');
$week_start = date('Y-m-d', $date_start);
$date_end = strtotime('next Sunday');
$week_end = date('Y-m-d', $date_end);

$user_data = array();

$this->db->select('*, COUNT(*) AS total');
$this->db->from($this->db->dbprefix . 'user');
$this->db->where('date_reg >=', $week_start);
$this->db->where('date_reg <=', $week_end);
$this->db->group_by('date_reg');
$query = $this->db->get();

for ($i = 0; $i < 7; $i++) {
    $date = date('Y-m-d', $date_start + ($i * 86400));
    $user_data[date('w', strtotime($date))] = array(
       'day'   => date('D', strtotime($date)),
       'total' => 0
    );
}

foreach ($query->result_array() as $result) {
    $user_data[date('w', strtotime($result['date_reg']))] = array(
       'day'   => date('D', strtotime($result['date_reg'])),
       'total' => $result['total']
     );
}

return $user_data;
} 


Code:

SELECT *, COUNT(*) AS total
FROM `user`
WHERE `date_reg` >= '2016-08-21'
AND `date_reg` <= '2016-08-27'
GROUP BY DAYNAME(date_reg)

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!

Looks good to me. Does it work?

I would have used the DateTime functions:

PHP Code:

$start = new DateTime('last Sunday');
$end = new DateTime('next Sunday');

// And then refer to them like this:
echo $start->format('d/m/Y');
echo $end->format('d/m/Y'); 


Although I am not sure it changes anything, or makes any difference, but they are a very powerful tool.

PHP Code:

$this->db->where('date_reg >=', $week_start);
$this->db->where('date_reg <=', $week_end); 


This would select a period of 8 days. You don't want to include next Sunday, use < instead of <=

There is a small problem in using:

PHP Code:

$start = new DateTime('last Sunday');
$end = new DateTime('next Sunday'); 


If you execute that code on a Sunday you select a period of 14 days, last Sunday is 7 days ago and next Sunday is in 7 days. In your case you only want the data from the current week so you would not notice it since no one could have a join date that lies in the future. But if you want to make your model function more flexible by adding a date (or year-weeknumber) as parameter for example to get the data from lets say last week you will run into this problem. So you can call getUserTotalByWeek('2016-33') and get the desired data.

I would also change:

PHP Code:

$this->db->select('*, COUNT(*) AS total'); 


To

PHP Code:

$this->db->select('date_reg, COUNT(date_reg) AS total'); 


It's bad practice to select everything when you only need use the data from 2 columns. Since your table is called 'user' I imagine it contains quite a few more columns then you are interested in at this function.

Hi,

Here is a helper function I use for week selection.
You can use the current or specific date.
Set the return format.
Use the current week or the previous week , if the supplied date falls on the same day as the start of the week.
Set the starting day for the week, useful if you need a "rolling" range. if set to  'any' the day of the current/set date will be use.

I use this to get list of task  todo and done.

PHP Code:

function get_week_dates($the_date = NULL, $format= 'Y-m-d', $if_same_day_use_last_week = FALSE,$day_start = 'sunday')
{
    $week = array('start'=>NULL,'end' => NULL);
    $date = new DateTime($the_date);
    
    $starting_day = ($day_start == 'any' ? $date->format('l'):$day_start);
    
    if($date->format('l') == ucfirst($starting_day)) 
    { 
        if(!$if_same_day_use_last_week)
        {
            $date->add(new DateInterval('P3D'));
        } 
        else {
            $date->sub(new DateInterval('P3D'));
        }
    
    }
    $week['start'] = date($format, strtotime('LAST '. strtoupper($starting_day) ,$date->getTimestamp()));
    
    if($date->format('l') <> ucfirst($starting_day))
    {
        $week['end'] = date($format, strtotime('NEXT '.strtoupper($starting_day).' -1 day',$date->getTimestamp()));
    }
    else{
        $week['end'] = date($format, $date->getTimestamp());
    }

    return $week;
} 


I hope this help.
Cheers

A good decision is based on knowledge and not on numbers. - Plato