Organize MySQL data by month using php/codeigntier

how to get month wise data in codeigniter
how to get current month data in codeigniter
how to get month wise data in mysql codeigniter
how to get month wise data in mysql php
how to get monthly data in codeigniter
how to get last one month data in mysql
how to get current week data in codeigniter
how to get current month in codeigniter

I need to organize the user login date by the month. Currently, I am doing it like this in code igniter:

My Model code:

public function getApril(){
  $this->db->select('*');
  $this->db->where('Date >=','2018-04-01');
  $this->db->where('Date <=','2018-04-30');
  $this->db->from('user_data');
  $count = $this->db->count_all_results();
  return $count;
}

public function getMarch(){
  $this->db->select('*');
  $this->db->where('Date >=','2018-03-01');
  $this->db->where('Date <=','2018-03-31');
  $this->db->from('user_data');
  $count = $this->db->count_all_results();
  return $count;
}

public function getFebruary(){
  $this->db->select('*');
  $this->db->where('Date >=','2018-02-01');
  $this->db->where('Date <=','2018-02-28');
  $this->db->from('user_data');
  $count = $this->db->count_all_results();
  return $count;
}

My controller code:

public function index(){
  $data["april"]= $this->login_model->getApril();
  $data["march"]= $this->login_model->getMarch();
  $data["february"]= $this->login_model->getFebruary();
  $this->load->view('main/header');
  $this->load->view('main/body', $data);
  $this->load->view('main/footer');
}

And in my view I just use echo to display the result, like so:

<p><strong><?php echo $april?></strong></p>

This seems like a very redundant method. I'm sure there is a better way of going about this.

I tried using 'group_by' like so: $this->db->group_by('MONTH(Date)'); but that only returns the number of months in the database and not the number of logins in each month.

if you want all months and years your suggestion with $this->db->group_by('MONTH(Date)') was pretty close

Something like

Model

public function getDataByMonth()
{
    $query = $this->db
        ->select('count(*) AS count, MONTH(Date) AS month, YEAR(Date) AS year')
        ->from('user_data')
        ->group_by('MONTH(Date)')
        ->group_by('YEAR(Date)')
        ->order_by('YEAR(Date)', 'DESC')
        ->order_by('MONTH(Date)', 'DESC')
        ->get();

    return $query->result();
}

Controller

public function index(){
    $this->load->view('main/header');
    $this->load->view('main/body', ['data' => $this->login_model->getDataByMonth()]);
    $this->load->view('main/footer');
}

view

<?php
foreach ($data AS $obj)
{
    $objDateTime = DateTime::createFromFormat('n', $obj->month);
?>
    <p><strong><?php echo $objDateTime->format('F').' '.$obj->year.' | '.$obj->count; ?></strong></p>
<?php
}
?>  

How to show total amount month and year wise CodeIgniter , In this example we will discuss about how to show total amount month and year wise from database table using CodeIgniter framework PHP. For show total  How to pass last month in where condition in PHP codeigniter? php mysql codeigniter. but i want just the last month data i dont want to use between function

You can use MONTH() method

And just use one function to access the data

public function getMonth($theMonthNumber){
  $this->db->select('*');
  $this->db->where('MONTH(Date)', $theMonthNumber);
  $this->db->from('user_data');
  $count = $this->db->count_all_results();
  return $count;
}

And, how to count each month log in?

Try with this query

SELECT MONTH(Date) as the_month, COUNT(*) as the_value
FROM user_data
WHERE user_data.user_id = "the_user_id_i_send"
GROUP BY MONTH(Date)

If you use this with codeigniter the function will look like this

function some_function_name($user_id){
     $query = $this->db->query('the same query i wrote before')
           ->result_array();
     return $query;
 }

the expected output should be an array with associative keys related to the month, Example output

  echo json_encode($query[0]); //the first month where the user has registers
  echo json_encode($query[3]); //the third month....and so on.. 

And of course you can filter this array.

(When i get home i will try to complement more the answer.)

Hope my answer helps you

How to show month in ascending order in PHP/mysql ?, Hide Copy Code. $query="SELECT * FROM `transactions` WHERE `dealer_id` = '$dealer_id' ORDER BY `_date` ASC"; Not a solution to your  In this codeigniter and chart js tutorial, We will learn how to implement or draw bar in codeigniter application using chart js. How to fetch monthly wise records from MySQL database & using this record how to draw bar chart with chart js with php codeigniter. We will fetch data monthly wise of the current year from php mysql database.

Here try this:

your model file

public function getDataByMonth($start, $end){
  $this->db->select('*');
  $this->db->where('Date >=', $start);
  $this->db->where('Date <=', $end);
  $this->db->from('user_data');
  $count = $this->db->count_all_results();
  return $count;
}

then create a months array that contains the name of the month, start and end dates like this then pass it to your controller function

$months = array(
    "january"   =>  array("01-01-2018", "31-01-2018"),
    "february"  =>  array("01-02-2018", "28-02-2018"),
    "march"     =>  array("01-03-2018", "31-01-2018")
);

last in your controller

public function index($months){
    foreach ($months as $key => $value) {
        $data[$key] = $this->login_model->getDataByMonth($value[0], $value[1]);
    }
  $this->load->view('main/header');
  $this->load->view('main/body', $data);
  $this->load->view('main/footer');
}

active record for [where date >= now()], The way you are doing it you are using MySQL's now() function - which If your database is stored using MySQL's date field type - then go with your Please someone try to replicate and see what may be a hack of some sort The filtration is done between two specific dates. When the user select the start and end date from the date picker, the ajax is been call and the request is to send to the date-filtration.php. It receives the two date and execute the query by filtering the data between two dates.

Change Log, Fixed a bug (#5692) - Database Forge didn't handle column nullability with Added 'ssl_verify' support to the 'pdo/mysql' Database driver. to word_is_countable() due to the former colliding with one introduced in PHP 7.3.0. Fixed a bug (#797) - Date Helper timespan() was using incorrect seconds for year and month. This may be very easy but I just can't find it

PHP cal_days_in_month() Function, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, MySQL Database The cal_days_in_month() function returns the number of days in a month for a specified year and calendar. Our second option is to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME will convert from PHP timestamp to DATETIME. The methods are used within the SQL query.

MySQL MONTH() Function, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java and XML. The MONTH() function returns the month part for a given date (​a number from 1 to 12). Copyright 1999-2020 by Refsnes Data.

Comments
  • This worked! Thanks! Just a note, for your view code I think you forgot to close the php tag. Replacing the last 2 lines with <?php } ?> should do the trick.
  • Just a followup question if I wanted to formate the date for week number, how would I do that?
  • by week number you have to group it by week i guess .., do you need support here ? because if so - pls open a new question .
  • This is an incremental improvement, but still fairly tedious.
  • I agree with cale_b. For example in 2019 I would need to create a new array.