CODEWITHSUNDEEP
phpcodeigniter-3
DMS-KH
DMS-KH

Reputation: 2797

How to select last months and calculate in Codeigniter3?

I want to calculate the count variable after I select date from db table and calculate from last month as below function

Issue I've select from DB but I can't check for last month and calculate count because I don't want to use sql in CI3 query.

Here is my function 

   public function last_month() {

        $date = date('m', time());
        $this->db->select('*');
        $this->db->from('user_agent');
        $q = $this->db->get();
        $count = 0;
        if ($q->num_rows() > 0) {
            foreach ($q->result() as $item) {
//                    echo (int)date("m", strtotime($item->timestamp));
                if ((int)date("m", strtotime($item->timestamp)) == (int)date("m", strtotime($date))) {
                    $count += $item->count;
                }
            }
        } else {
            return FALSE;
        }
        return $count;
    }

Upvotes: 0

Views: 122

Answers (1)

Tpojka
Tpojka

Reputation: 7111

Unfortunatelly to your wishes MYSQL native functions would be best way to calculate something like that. If you would like to add months as you started above like 9+9+9, than

SELECT SUM(MONTH(NOW())) AS monthCount FROM  `ci_sessions` WHERE `last_activity` > UNIX_TIMESTAMP(STR_TO_DATE("{$currentMonth}", '%Y-%m-%d')) - 1;

, but if you would like to know just amount of rows fit current month than

SELECT COUNT(`last_activity`) AS monthCount FROM  `ci_sessions` WHERE `last_activity` > UNIX_TIMESTAMP(STR_TO_DATE("{$currentMonth}", '%Y-%m-%d')) - 1;

For passing last day of previous month that you would need to use dynamically in PHP before MySQL you should find it this way for example

$currentMonth = date('Y-m-01');

Of course you would need to adapt code to fit your tables and column names.

Upvotes: 1

Related Questions