Mysql Query Issue [Solved]

[eluser]ywftdg[/eluser]
I had a post about this topic many months ago, back then there was never an answer. Many months later, I have yet to solve this. Can anyone tell help me figuire out why the code below will not work? It is checking to see if the month is January, if so set the year back -1 if they want to run a sales report for "Previous Month." This will not work in my model, anytime I have 2 date statements like that, it results in nothing. I have also defined the querydate variable at the top of my model, that is returning correctly as tested.

Code:

$this->querydate = date("m"); // Get the current date
    
        
if ($datepost == '6') {     // Last Month BUG
            if ( $this->querydate == '1' ) {    $this->rollbackyear = '-1'; }    // Check Date
            
            $this->db->where('MONTH(rTimeStamp) = MONTH(CURDATE())-1');
            $this->db->where('YEAR(rTimeStamp) = YEAR(CURDATE())' . $this->rollbackyear . '');
            
}

[eluser]xwero[/eluser]
Why not do it all in mysql?

WHERE DATE_FORMAT(rTimeStamp,'%Y-%m') = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH,'%Y-%m')

[eluser]ywftdg[/eluser]
xwero, its funny because you gave me this same response last year. I think I finally get what your saying about let mysql do the work, as far as making the choice to go back a year or not. Thanks!

Now will try and learn how to apply this to quarter also...

[eluser]xwero[/eluser]
quarter can be used but only in mysql5

[eluser]ywftdg[/eluser]
Trying quarter, but it doesn't seem to fit into the concept above so easily for me. I am running mysql 5, so should work with correct code..

[eluser]xwero[/eluser]
For quarter you need to do something like

QUARTER(rTimeStamp) = QUARTER(CURDATE() - INTERVAL 1 QUARTER)

[eluser]ywftdg[/eluser]
Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near '(CURDATE() - INTERVAL 1 QUARTER)

I am running Mysql 5.0.41 and other basic Quarter stuff works fine.

Code:

if ($datepost == '2') {        // Last Quarter BUG

            $this->db->where('QUARTER(rTimeStamp) = QUARTER (CURDATE() - INTERVAL 1 QUARTER)');
            
        }

[eluser]xwero[/eluser]
It's possible the subtraction returns a numeric value where the quarter function needs a mysql timestamp so you could try to format it.

[eluser]ywftdg[/eluser]
Tried numerous combinations nothing yet. Google time on quarters and mysql, maybe something will come back useful...

[eluser]ywftdg[/eluser]
Solved:

Code:

if ($datepost == '2') {        // Last Quarter
            
            $this->db->where('QUARTER(rTimeStamp) = QUARTER(DATE_ADD(CURDATE(), INTERVAL -3 MONTH))');
            
            if ( $this->querydate == '01' || $this->querydate == '02' || $this->querydate == '03' ) {    
                $this->db->where('YEAR(rTimeStamp) = YEAR(CURDATE())-1');
            } else {
                $this->db->where('YEAR(rTimeStamp) = YEAR(CURDATE())');
            }
                
        }