Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Using SQL to extract entries from 1 January of the current year
I have a section called ‘incidents’ to post articles about incidents our fire brigade attends.
What I now want to do is use smd_query to count the total number of incidents this year (ie. for any given year) and show this on the homepage. But I’m struggling with the right SQL query to use.
select count(*) from textpattern where section='incidents' correctly calculates the total number of incidents.
But when I try to calculate using the posted date of the article, I just can’t seem to get the SQL query right. For example, select count(*) from textpattern where section='incidents' where year(Posted) = '2010' doesn’t work.
I’ve been trawling around on Google to find a solution but I’m stumped. Can anyone offer an idea?
Thanks
Offline
Re: Using SQL to extract entries from 1 January of the current year
aslsw66
Sooo close. Try:
select count(*) from textpattern where section='incidents' AND year(Posted) = '2010'The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: Using SQL to extract entries from 1 January of the current year
OK, fair enough, but in my defence it’s after midnight here! I think I will wait until tomorrow to insert this into the page and make it work – I’m at the stage where I will probably just break something.
Thanks again for your help. smw_query is an awesome plugin.
Offline
Re: Using SQL to extract entries from 1 January of the current year
aslsw66 a écrit:
Thanks again for your help.
smw_queryis an awesome plugin.
Sure it too late for you, (smd_query become smw_query) ;)
Offline