Orderby Limit # of Days

5 posts by 2 authors in: Forums > CMS Builder
Last Post: April 5, 2011   (RSS)

By ILLUME-MEDIA-LABS - April 3, 2011 - edited: April 3, 2011

Hi,

I have implemented a simple hit counter to keep track of the # of hits. I need to pull a list of 10 articles with the most hits within the past 30 days. I use the following code and get the following error. Can someone please help? Thanks!

//trending
list($hitsRecords, $hitsMetaData) = getRecords(array(
'tableName' => 'am_articles',
'where' => "date = DATE_SUB(date,30 DAY) AND approved = 1",
'orderBy' => 'hits+0 DESC',
'limit' => 10,
));

MySQL 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 '30 DAY) AND approved = 1) AND am_articles.hidden = 0 ORDER BY hits+0 DESC LIM' at line 3

Re: [Illume Magazine] Orderby Limit # of Days

By robin - April 4, 2011

Hi,

Looks like you're close, you just need to change your DATE_SUB. The following where statement should help.
'where' => "date = DATE_SUB(NOW(),INTERVAL 30 DAY) AND approved = 1",
The documentation for MySQL date functions is here:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Hope that helps,
Robin
Robin
Programmer
interactivetools.com

Re: [robin] Orderby Limit # of Days

By ILLUME-MEDIA-LABS - April 4, 2011

Hi Robin,

Thanks for the help. That got rid of the error message, but I am not getting any content.

Re: [Illume Magazine] Orderby Limit # of Days

By robin - April 5, 2011

Hey,

Ah I see, the 'where' I sent you yesterday will only pull records from 30 days ago. We need to make it a range! Here is the 'where' with a range from 30 days ago until now.
'where' => "date BETWEEN DATE_SUB(NOW(),INTERVAL 30 DAY) AND NOW() AND approved = 1 ",

Hope that helps,
Robin
Robin
Programmer
interactivetools.com