CODEWITHSUNDEEP
phpmysqlcodeigniter
Rajan
Rajan

Reputation: 2425

How to pass last month in where condition in PHP codeigniter?

i want to fetch records of last month and last week from my database.

I have login and logout data in database also i have filed called date_data which is the date.

Now i am fetching my data by this:

public function monthly_login($emp_id = NULL)
{

      $emp_id = $this->session->userdata('emp_id');

      $this->db->select('*');

      $this->db->from('daily_data2');

      //$this->db->where('MONTH(date_data)', date('1-m'));

      $this->db->where('users.emp_id',$emp_id);

      $this->db->where('entry >','100');

      $this->db->order_by("date_data","ASC");

      $this->db->join('users', 'users.emp_id = daily_data2.emp_id','inner');

      $query = $this->db->get();



       $res   = $query->result();        

      return $res;

 }

I dont know what should i pass in my where condition so that i get last month and last week's data

Upvotes: 2

Views: 4364

Answers (2)

Ajay Makwana
Ajay Makwana

Reputation: 2372

try this...

For Week :

$this->db->where('date_data <=','DATE_ADD(NOW(),INTERVAL 7 DAYS )');

For Month :

$this->db->where('date_data <=','DATE_ADD(NOW(),INTERVAL 30 DAYS )');

For Last Month start from 1st date

$this->db->where('date_data <=','DATE_ADD(NOW(),INTERVAL 30 DAYS,"%Y-%m-01")');

Try Either of below:

$this->db->where('date_data <=','DATE_ADD(NOW(),INTERVAL 1DAY - INTERVAL 1 MONTH,"%Y-%m-01")');

or

$this->db->where('timestamp >=','DATE_ADD(NOW(),INTERVAL 1DAY - INTERVAL 1 MONTH,"%Y-%m-01")');

Upvotes: 3

Anand thakkar
Anand thakkar

Reputation: 489

$this->db->where("date_data BETWEEN {$startDate} AND {$endDate} ");

Here $startDate and $endDate you can Calculate via php's date and strtotime function as follows

For last 7 days 

 $startDate = date('y-m-d',strtotime("-1 week"), "\n";)
 $endDate   = date('y-m-d',strtotime("now"), "\n";)

For last 30 days 

 $startDate = date('y-m-d',strtotime("-30 days"), "\n";)
 $endDate   = date('y-m-d',strtotime("now"), "\n";)

So you can use in this way

As For your Requirement based on your comment that you want the data of last month regardless of the current date in that case you have to calculate the date range as follows

$startDate = new DateTime();
$startDate->modify( 'first day of last month' );

$endDate   = new DateTime();
$endDate->modify( 'last day of last month' );

In case you are using DateTime object in that case you have to update your Query like this. 

$this->db->where("date_data BETWEEN '" . $startDate->format( 'Y-m-d' ) . "' AND '" . $endDate->format( 'Y-m-d') . "' ");

Upvotes: 4

Related Questions