Welcome Guest, Not a member yet? Register   Sign In
Count the number of new users for current month
#1

(This post was last modified: 05-09-2017, 10:37 PM by wolfgang1983.)

I have a function below where it gets the total number of users for each month.

But I would like to know how can I make sure it only shows total number of new users for the current month.

Currently shows only previous month 


Function


PHP Code:
public function get_monthly_user_total() {
$userdata = array();

$this->db->select('COUNT(user_userdata_id) as total, date_added');
$this->db->from('user_userdata');
$this->db->where('YEAR(date_added) = YEAR(NOW())');
$this->db->group_by('MONTH(date_added)');
$query $this->db->get();

foreach (
$query->result_array() as $result) {
$userdata[date('n'strtotime($result['date_added']))] = array(
'month' => date('M'strtotime($result['date_added'])),
'total' => $result['total']
);
}

return 
$userdata;


Controller

PHP Code:
<?php

class Newusers extends MX_Controller {

    public function 
__construct() {
        
parent::__construct();
    }

    public function 
index() {
        foreach (
$this->get_monthly_user_total() as $result) {
            
$data['newusers'][] = array(
                
'month' => $result['month'],
                
'total' => $result['total']
            );
        }

        
$this->load->view('widgets/newusers'$data);
    }

    public function 
get_monthly_user_total() {
        
$userdata = array();

        
$this->db->select('COUNT(user_userdata_id) as total, date_added');
        
$this->db->from('user_userdata');
        
$this->db->where('YEAR(date_added) = YEAR(NOW())');
        
$this->db->group_by('MONTH(date_added)');
        
$query $this->db->get();

        foreach (
$query->result_array() as $result) {
            
$userdata[date('n'strtotime($result['date_added']))] = array(
                
'month' => date('M'strtotime($result['date_added'])),
                
'total' => $result['total']
            );
        }

        return 
$userdata;
    }


[Image: 3Lx69chyhkZ1.png]

[Image: 3Lx5F6ztzx1f.png]
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#2

(This post was last modified: 05-10-2017, 02:51 AM by neuron.)

Try just adding condition to your query

PHP Code:
$this->db->select('COUNT(user_userdata_id) as total, date_added');
$this->db->from('user_userdata');
$this->db->where('YEAR(date_added)''YEAR(NOW())');
$this->db->where('MONTH(date_added)''MONTH(NOW())');
$this->db->group_by('MONTH(date_added)');//you can delete this line as your selecting only last month count() will group them
$query $this->db->get(); 
Reply
#3

(05-10-2017, 02:50 AM)neuron Wrote: Try just adding condition to your query

PHP Code:
$this->db->select('COUNT(user_userdata_id) as total, date_added');
$this->db->from('user_userdata');
$this->db->where('YEAR(date_added)''YEAR(NOW())');
$this->db->where('MONTH(date_added)''MONTH(NOW())');
$this->db->group_by('MONTH(date_added)');//you can delete this line as your selecting only last month count() will group them
$query $this->db->get(); 

For some reason it shows jan for the month it should show the total new users for current month may.
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#4

(This post was last modified: 05-11-2017, 10:50 AM by PaulD. Edit Reason: forgot to update a variable, edited now )

Hi,

Try searching between the start of the month and the end of the month with dates set like this:

PHP Code:
// get this month 
        //(but you might set this as a variable so you can look at different months too)
        
$month date('F Y');
        
        
// set start of month
        
$date = new DateTime($month);
        
$start_date $date->format('Y-m-d G:i:s');
        
        
// set end of month and time to last second of month 
        
$date->modify('last day of this month')->setTime(23,59,59);
        
$end_date $date->format('Y-m-d G:i:s');
        
        
//  do a search with dates
        
$count $this->db->from('user_userdata')
            ->
where('date_added >='$start_date)
            ->
where('date_added <='$end_date)
            ->
count_all_results();
        
        return 
$count

Simple and works a treat. And by passing a month year option into it as a model call manually, you can count anything in any month you like. I use this for generating date based reports for users, with a year and month picker.
Reply
#5

(05-10-2017, 10:29 AM)PaulD Wrote: Hi,

Try searching between the start of the month and the end of the month with dates set like this:

PHP Code:
       // get this month 
 
       //(but you might set this as a variable so you can look at different months too)
 
       $month date('F Y');
 
       
        
// set start of month
 
       $date = new DateTime($date_range);
 
       $start_date $date->format('Y-m-d G:i:s');
 
       
        
// set end of month and time to last second of month 
 
       $date->modify('last day of this month')->setTime(23,59,59);
 
       $end_date $date->format('Y-m-d G:i:s');
 
       
        
//  do a search with dates
 
       $count $this->db->from('user_userdata')
 
           ->where('date_added >='$start_date)
 
           ->where('date_added <='$end_date)
 
           ->count_all_results();
 
       
        return $count


Simple and works a treat. And by passing a month year option into it as a model call manually, you can count anything in any month you like. I use this for generating date based reports for users, with a year and month picker.

It seems to work will do some more testing later the $date_rage I change to the $month variable?

Thanks for help
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply
#6

(05-10-2017, 04:39 PM)wolfgang1983 Wrote: It seems to work will do some more testing later the $date_rage I change to the $month variable?

Thanks for help

Yes, sorry, the date range is what I pass to a similar function in my own code, was editing it all down here for you but missed that one. Of course in the simplified version above $date_range is $month.

Normally I would pass either a month and year (for a particular month to search on) or a range like this month, last month, this quarter, last quarter, this year, last year etc. I translate all those into start dates and end dates and then do the search on whatever records are being searched as shown in the example.

Have edited the original post by me as well.

Best wishes,

Paul.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB