princeads Posted March 9, 2008 Share Posted March 9, 2008 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 Quote Link to comment Share on other sites More sharing options...
uniflare Posted March 10, 2008 Share Posted March 10, 2008 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 Quote Link to comment Share on other sites More sharing options...
teng84 Posted March 10, 2008 Share Posted March 10, 2008 $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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.