Sql query with date where clause

[eluser]Todlerone[/eluser]
Hello all and TY in advance. I have a database timestamp entry in the form "2011-03-21 03:02:59". What I want to do is do a sql query to get all entries with the current month, then all entries in the previous month. I tried this but it isn't wotking.

Code:

$this->db->where('demo_created_on', unix_to_human(strtotime("-1 month"), TRUE, 'us'));

Any ideas? TY again

[eluser]JHackamack[/eluser]
Looking at unix_to_human it says it uses the prototype of: YYYY-MM-DD HH:MM:SS AM/PM. MySQL uses a 24 hour clock so there will never be a AM/PM. What is the output of $this->db->last_query() after you try and execute the query?

[eluser]Todlerone[/eluser]
I get nothing back. Nor any errors. I'm just trying to get all entries for each month(ongoing) based on the date created field. Would it be easier to enter dates into the database as a unix_timestamp then convert them or can it easily be done with data in the form "2011-03-21 03:02:59"

TY

[eluser]JHackamack[/eluser]
I think I see what you're trying to do.

Try using:

Code:

$this->db->where("demo_created_on >= DATE_FORMAT(date_sub(now(), interval 1 month),'%Y-%m')",FALSE);

This will get you all entries from the current month and all entries from the previous month:
Example:
If you ran it today you'd get everything from Feb 1, 2010 to Now.

[eluser]Todlerone[/eluser]
Ok, I'm going to try something else inorder to reuse some code. I have tried this but can't get it to work.

I have created a helper file named stats_helper.php and saved in in my application/helper folder.

Code:

<?php

    function tmonth($mth){
        $allpts = $this->Stats_model->all_patients();
        $x=0;
        foreach ($allpts as $ap) :
            $month_sel = date('m', strtotime($mth));//vary the month needed
            $t = date("m", strtotime($ap['demo_created_on']));
            if ($t == $month_sel){
                $x++;
            }
        endforeach;
    }
?>

In my controller I have

Code:

class Stats_main extends Controller {
    
    function Stats_main(){
        parent::Controller();
    }
    
    function index(){
        $this->load->model('Stats_model');
        $this->load->helper('stats');
                                    
        $data['new_patients_current_month'] = '';
        $month = date("m"); // current month
            
        $data['last_month_stats'] -> tmonth('-1 month');
            
        $this->title="Stats";    
        $this->thisPage="Stats";    
        $this->load->view('header',$data);
              $this->load->view('stats/stats_main',$data);
        $this->load->view('sidebars/sidebar_stats_main');
        $this->load->view('footer');
    }// index
}// end class

I can't get it to work.

[eluser]JHackamack[/eluser]
Do you see any errors that are popping up? Have you turned on error logging? Keep in mind helpers might not be the best place to put in calls to models (that might best be reserved for Libraries, but also keep in mind helpers and models won't have the CI $this object (http://ellislab.com/codeigniter/user-gui...aries.html)

[eluser]Todlerone[/eluser]
Could I simply place my helper function in my Stats_main class and call it there?

[eluser]Todlerone[/eluser]
I was able to get this to work perfectly within the main controller, ie, no helper call.

Code:

$allpts = $this->Stats_model->all_patients();
    $lstmth=0;
    foreach ($allpts as $ap) :
        $month_sel = date('m', strtotime('-1 month'));
        $t = date("m", strtotime($ap['demo_created_on']));
        if ($t == $month_sel){
            $lstmth++;
        }
    endforeach;
    $data['new_patients_current_month'] = $lstmth;

I just want to vary the date('m', strtotime('-3 month')) line several times. How would I go about reusing this code? TY

[eluser]Todlerone[/eluser]
I think I will concentrate on getting the proper values from the model first.

[eluser]Todlerone[/eluser]
Great I got it to work using the model. I used this in my model

Code:

function stats_mth($mth){
     $query = $this->db->query("SELECT * FROM `demographics` WHERE MONTH(`demo_created_on`) = $mth");
     if($query->num_rows()>0){
    return $query->num_rows();
     }        
}

And my controller call is this:

Code:

$data['new_people_last_month'] = $this->Stats_model->stats_mth(date("m")-1);

And finally my view:

Code:

echo "New people in ".date("F", mktime(0, 0, 0, date("m")-1, date("d"),   date("Y"))).": ".$new_people_last_month."<br/>";

Produces a result like this-------------> New people in February: 20


My little problem now is that if I try to go back three months that takes me into December and I don't get any data results.

Any ideas...TY