sql query module to find entries not older than 31 days
Development and Programming
I want to display a list of categories and also the number of entries in them (as described here But the entries must not be older than 31 days.
So I used the exp:query module like this:
{exp:query sql="SELECT count( exp_category_posts.entry_id ) AS total_items,
exp_category_posts.cat_id, exp_categories.cat_name as category_name, exp_categories.cat_url_title as category_url_title
FROM exp_categories, exp_category_posts, exp_weblog_titles
WHERE exp_category_posts.cat_id = exp_categories.cat_id
AND exp_categories.site_id = 1
AND exp_categories.group_id = 4
AND exp_weblog_titles.entry_id = exp_category_posts.entry_id
AND exp_weblog_titles.weblog_id = 69
AND exp_weblog_titles.entry_date > <?php echo time() - (31 * 24 * 60 * 60); ?>
GROUP BY exp_categories.cat_name"}
<a href="http://{path=weblog/page}C{cat_id}/">{cat_name}</a> ({count})
{/exp:query}But I get a SQL error so I guess the row
AND exp_weblog_titles.entry_date > <?php echo time() - (31 * 24 * 60 * 60); ?>is the problem.
Can someone please explain what I am doing wrong? Or give me another solution?
[Mod Edit: Moved to the Development and Programming forum]
Patrick,
First of all, if PHP isn’t enabled in your template on Input, then that code won’t work. HOWEVER, you don’t need PHP to do what you’re trying to do at all! MySQL has a perfectly find set of date functions built in.
Just replace your problem line with:
AND exp_weblog_titles.entry_date >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 31 DAY)That says, “take the current date, subtract 31 days, and then turn it into an epoch date (otherwise known as a UNIX timestamp).”
Thanks alot! It works just the way I want!
You’re welcome! If you have any more mySQL/query questions, ask them in the forum (because it’s important for other people to be able to see the answers!) – but please feel free to ALSO PM me to tell me “hey, i’ve got an SQL question, here’s the thread link.” Because i <3 SQL queries, and i’m always happy to help, but i don’t always see all the forum posts.