[geeklog-devel] Bug and fix in What's New routine...

[Dirk Haun]

Rob Griffiths <webteam at macosxhints.com> wrote:

>Marc' re-written query is:
>
>   $sql = "SELECT DISTINCT count(*) AS dups, type, question,  
>{$_TABLES['stories']}.title, {$_TABLES['stories']}.sid, qid,  
>max(comments.date) as lastdate FROM {$_TABLES['comments']} LEFT JOIN  
>{$_TABLES['stories']} ON (({$_TABLES['stories']}.sid =  
>{$_TABLES['comments']}.sid) AND ({$stsql})) LEFT JOIN  
>{$_TABLES['pollquestions']} ON ((qid = {$_TABLES['comments']}.sid) AND  
>(({$posql}))) WHERE ({$_TABLES['comments']}.date >= (NOW() - INTERVAL  
>{$_CONF['newcommentsinterval']} SECOND)) AND ((({$stwhere})) OR  
>(({$powhere}))) GROUP BY {$_TABLES['comments']}.sid ORDER BY 7 DESC  
>LIMIT 25";

Some comments:

- max(comments.date) should read max({$_TABLES['comments']}.date)
- I'd suggest using DATE_SUB instead of the '-' to make it work with old
versions of MySQL
- The original LIMIT is 15 comments, not 25


>If someone could clean up the formatting and get this into the next  
>minor update, it'd be greatly appreciated.

This (with the above changes) is now in CVS and also running on both
geeklog.net and geeklog.info.

bye, Dirk


-- 
http://www.haun-online.de/
http://geeklog.info/