[SOLVED] Easier Way To Write Query - select data within last year

[xander85]

I wrote a query to select the sum of products ordered in each month over the past year. However, I want my query to be static and automatically update itself. I want to select the sum of each month and I'm using the following query:

 

SELECT SUM( xcart_order_details.amount ) 
FROM xcart_orders LEFT JOIN xcart_order_details ON xcart_orders.orderid =  xcart_order_details.orderid 
WHERE MONTH( FROM_UNIXTIME( date ) ) = '".$i."' AND period_diff( concat( ( left( curdate( ) , 4 ) ) , ( substr( curdate( ) , 6, 2 ) ) ), concat( ( left( FROM_UNIXTIME( date ) , 4 ) ) , ( substr( FROM_UNIXTIME( date ) , 6, 2 )))) <= '12' 
AND xcart_order_details.productcode = '".$_POST['productcode']."'

 

The query takes the curdate() and converts its to the format: YYYYMM, and converts the date column (unix timestamp format) and converts it to YYYYMM and then calculates the difference between curdate() and date column (has to be <= 12, so it is within a year) and the first where condition selects where the month of the order is equal to 1,2,3,4.....12 (for all months). I'm using a for loop to change $i.

 

This query seems very clumsy and there has to be an easier way to write this. Keep in mind I want it to be dynamic so I don't have keep changing the code on the page I wrote every month.

[beebum]

Try this:

 

...WHERE MONTH( FROM_UNIXTIME( date ) ) = '".$i."' AND UNIX_TIMESTAMP(now()) - date <= 31536000;

[xander85]

I want to be able to select each month from the past year:

 

For example, from the current date I want:

Jan 08, Dec 08, nov, oct, ....

 

Then next month, shift everything forward one month.

 

How would I go about doing this? Do I need twelve individual queries? My own concern is since each month isn't the same amount of days that a static value will not work and that is why I chose to use the period_diff function.

 

 

[beebum]

I thought you were using $I to specify the month.

[xander85]

Whoops, I feel dumb. You're right. That should work great. Thanks!