Forums

Hi folks,
​
I need to display data from last month (not the last 30 days). My form has a "created" field and I tested the query

SELECT * FROM `fz7dk_chronoforms_data_voluntarios` WHERE YEAR(created) = YEAR(CURRENT_DATE - INTERVAL1 MONTH) AND MONTH(created) = MONTH(CURRENT_DATE - INTERVAL1 MONTH)

on phpMyAdmin and it works fine. As there's no need to keep the WHERE statement on the Where Condition box, it was simplified to

YEAR(created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

which produces a 1064 error

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 '' at line 1                      

Surrounding the whole query with double quotes I can see it, though it's not effective

WHERE "YEAR(`Mes`.`created)` = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(`Mes`.`created)` = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)" = ''

I Noticed the

(`Mes`.`created)`

near YEAR and MONTH. I am no DBA, but it seems to me that the fourth ` on each case should be inside the parentheses

(`Mes`.`created`)

Is there any way to solve this?

You're making it all look very complicated. Should also probably have a look at the manual for the proper syntax.
​
Until then, in your where condition just put
​

created/>=:{date:Y-m-d$first day of last month}
created/<=:{date:Y-m-d$last day of last month}

Don't copy paste, forums adds hidden formatting characters

Thank you healyhatman
​
You might be right, probably I was making it more difficult, but anyway, I was baffled by the fact that it worked as a query, but not in the form.