Welcome Guest, Not a member yet? Register   Sign In
Getting last months data from database
#1

Hi there,

I am trying to work out how I can get the previous months results from my database.  This is what I have so far but I just cannot figure out what should be in the where line.

PHP Code:
public function get_last_month(){
        $this->db->select('*');
        $this->db->from('test_table');
        $this->db->where('date''');
        $query $this->db->get();
        return $query->result();
    

Can anyone help please?

Thanks,

Doomie
Reply
#2

(08-25-2016, 08:34 AM)Hi Doomie22,here is the solution for getting last month data: Wrote:
PHP Code:
public function get_last_month()
{
    
// Get Last Month
    
$date = new DateTime();
    
$date->modify("last day of previous month");
    
$last_month $date->format("Y-m-d");

    
// Your Query
    
$this->db->select('*');
    
$this->db->from('test_table');
    
$this->db->where('date'$last_month);
    
$query $this->db->get();
    return 
$query->result();


and here is the solution if you want to get data with custom user input date:

PHP Code:
public function get_last_month$user_input_date )
{
    
// Get data with user provided month
    
$this->db->select('*');
    
$this->db->from('test_table');
    
$this->db->where('date'$user_input_date);
    
$query $this->db->get();
    return 
$query->result();

 

doomie22Hi there,

I am trying to work out how I can get the previous months results from my database.  This is what I have so far but I just cannot figure out what should be in the where line.

PHP Code:
public function get_last_month(){
        $this->db->select('*');
        $this->db->from('test_table');
        $this->db->where('date''');
        $query $this->db->get();
        return $query->result();
    

Can anyone help please?

Thanks,

Doomie
Reply
#3

First, try to get the month number AND year from the previous month.
PHP Code:
$pm = (int) date('n'strtotime('-1 months'));
$pmy = (int) date('Y'strtotime('-1 months')); 

Now you're ready to build your query:
PHP Code:
public function get_last_month(){
        
$this->db->select('*');
        
$this->db->from('test_table');
        
$this->db->where('MONTH(date)'$pm);
        
$this->db->where('YEAR(date)'$pmy);
        
$query $this->db->get();
        return 
$query->result();
    } 
You need the year in case the current month is January!
Hope this will help.
Reply
#4

(08-25-2016, 11:39 AM)Wouter60 Wrote: First, try to get the month number AND year from the previous month.
PHP Code:
$pm = (int) date('n'strtotime('-1 months'));
$pmy = (int) date('Y'strtotime('-1 months')); 

Now you're ready to build your query:
PHP Code:
public function get_last_month(){
 
       $this->db->select('*');
 
       $this->db->from('test_table');
 
       $this->db->where('MONTH(date)'$pm);
 
       $this->db->where('YEAR(date)'$pmy);
 
       $query $this->db->get();
 
       return $query->result();
 
   
You need the year in case the current month is January!
Hope this will help.

This worked perfectly.

Thank you so much.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB