-
wolfgang1983
Senior Member
-
Posts: 627
Threads: 271
Joined: Oct 2014
Reputation:
7
05-09-2017, 10:36 PM
(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; } }
There's only one rule - please don't tell anyone to go and read the manual. Sometimes the manual just SUCKS!
-
neuron
Member
-
Posts: 198
Threads: 39
Joined: Nov 2016
Reputation:
8
05-10-2017, 02:50 AM
(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();
-
wolfgang1983
Senior Member
-
Posts: 627
Threads: 271
Joined: Oct 2014
Reputation:
7
(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!
-
PaulD
Posting Freak
-
Posts: 1,061
Threads: 42
Joined: Mar 2015
Reputation:
73
05-10-2017, 10:29 AM
(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.
-
wolfgang1983
Senior Member
-
Posts: 627
Threads: 271
Joined: Oct 2014
Reputation:
7
(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!
-
PaulD
Posting Freak
-
Posts: 1,061
Threads: 42
Joined: Mar 2015
Reputation:
73
(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.
|