SQL Help

[princeads]

Hiya,

 

Just got a problem with my sql statement that links my PHP to MYSQL database. My sql statement is below but I'm unsure how to change the statement to only retrieve the top 5 news results based upon this calculation: Number of hits (cmsnews.hits) divided by the number of days since created date (cmsnews.date). 

 

$sql  = "SELECT cmsnews.newsID, cmsnews.title, cmsnews.article, cmsnews.status, cmsnews.date, cmsnews.hits,  cmscontentstatus.statusID

        FROM cmsnews, cmscontentstatus

        WHERE cmsnews.status = cmscontentstatus.statusID AND cmscontentstatus.status = 'Published'

        ORDER BY cmsnews.hits DESC

        LIMIT 0, 5 ";

 

Any help would be great, cheers

AB

[uniflare]

im really bad with mysql but i believe it would be somethign along the lines of:

SELECT * FROM `cmsnews` ORDER BY (cmsnews.hits/((UNIX_TIMESTAMP()/8400)-(cmsnews.date/86400))) DESC

 

though this does not seem to work... hopefully someone can fix this query

[teng84]

$sql  = "SELECT cmsnews.newsID, cmsnews.title, cmsnews.article, cmsnews.status, cmsnews.date, cmsnews.hits,    cmscontentstatus.statusID,(cmsnews.hits /DATEDIFF('end','startdate')) as odr
         FROM cmsnews, cmscontentstatus
         WHERE cmsnews.status = cmscontentstatus.statusID AND cmscontentstatus.status = 'Published'
         ORDER BY odr DESC
         LIMIT 0, 5 ";

 

maybe this?