Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

function where date = last month WordPress

  • SOLVED

Hello,
This is my select statement, which works great! It returns the sum for the previous calendar month as expected. But it won't work in January, 2011 :(

SELECT
SUM(T.quantity) as sum
FROM
wp_transactions T,
wp_items I,
wp_location L
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND T.trans_type= 'Inventory'
AND MONTH(T.date)=MONTH(CURDATE())-1


Please correct the WHERE T.date = last month part of this statement. I have tried many 'solutions' I've found on line, none have worked.

Answers (3)

2011-09-09

John Cotton answers:

Try this as your WHERE clause:


( YEAR(T.Date) = YEAR(CURDATE()) AND MONTH(T.Date) = MONTH(CURDATE()) - 1 ) OR
( YEAR(T.Date) = YEAR(CURDATE()) - 1 AND MONTH(T.Date) = MONTH(CURDATE()) + 11 )


Patricia Moff comments:

oh my gosh, that is so tricky, it just might work! It works when I execute it on the db, but I can't check the 'OR' unless I change the date on the server.

Have you ever used this before?

2011-09-09

Duncan O'Neill answers:

Hi, what plugin are you using which creates the extra tables please?

cheers,


Patricia Moff comments:

Sorry, It's a plugin I am writing (it won't be released, as it's for internal office use only)


Patricia Moff comments:

Do you think this will work? It can't really be tested without changing the current date on the server, unfortunately:

AND month(T.date) = CASE month(now()) WHEN 1 THEN 12 ELSE month(now()) - 1 END


Duncan O'Neill comments:

Patricia,

I really can't tell you if that will work or not, because 1) it looks like you know more about MySQL selects than I do 2) I have no idea of the structure of your db, or the table you're working with.

All I can do is to make suggestions, which may be way off the mark, but here goes;

1) If you're not already using it, does your host provide phpmyadmin access to your database, so that you can test these SQL statements by typing them in directly?

2) I think your T.date column needs to be of data type datetime.

3) Unlikely, but could this be an issue with reserved words?
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
I guess you could test this by putting back-ticks ( ` ) around the date string in the select statement.

Hope this helps.

I hope this helps.


Patricia Moff comments:

Duncan,
yeah, I can execute SQL directly on the database (a life saver, btw). The T.date column is type=date. If displays as yyyy-mm-dd.

The problem with the way I have the select statement written now (I think), is it won't work in January of any given year, as 1-1=0, not 12.

As I said above, I think

AND month(T.date) = CASE month(now()) WHEN 1 THEN 12 ELSE month(now()) - 1 END
might work, but I won't know until January. It also might get the year wrong ;(

2011-09-09

Gabriel Reguly answers:

Hi Patricia,

You can generate the last month from PHP too, as in


$last_month = date( 'n', mktime( 0, 0, 0, date('n') - 1, date('d'), date('Y') ) );

$query = '
SELECT SUM(T.quantity) as sum
FROM wp_transactions T,
wp_items I,
wp_location L
WHERE T.item_id=I.item_id
AND T.location_id=L.location_id
AND T.trans_type= "Inventory"
AND MONTH(T.date) = ' . $last_month;


This way you can test without changing the server date, as in

$last_month = date( 'n', mktime( 0, 0, 0, date('n', mktime( 0, 0, 0, 1, 1, 2011 ) ) - 1, date('d'), date('Y') ) );


This would get you december 2010.

Regards,
Gabriel