Buy Now
Sign in

WHERE condition on Read Data

cbahiana , November 13 2018
C
cbahiana 13
November 13 2018 #381491
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?
healyhatman 5.7
November 14 2018 #381495
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
Web developer at SkySpider.com.au
I don't work for ChronoEngine but I do accept donations paypal.me/healyhatman

You can now copy+paste code from forums
C
cbahiana 13
November 14 2018 #381505
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.