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!
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
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.
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
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
Programmer
interactivetools.com
Re: [robin] Orderby Limit # of Days
Hi Robin,
Thanks for the help. That got rid of the error message, but I am not getting any content.
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.
Hope that helps,
Robin
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
Programmer
interactivetools.com
Re: [robin] Orderby Limit # of Days
Awesome. That worked. Many thanks. You are now a rockstar, along with Ross & Damon!