ORDER BY Algorithm


I am working on a php/mysql driven website where users can make posts.
Other users can vote up/down on these posts.

I am working on an Algorithm to order these posts.
I want the posts with the highest NET points to be displayed on top, however I also want to give priority to new posts.

This is what I have so far,

ORDER BY points_up - points_down DESC, timestamp DESC

However I want to do something along these lines.

(points_up - points_down) / minutes 

The minutes being the number of minutes the post has been active. However I’m not sure how to implement the minutes feature using my timestamp.

i thought you actually wanted to divide by minutes since the post has been active

what is the DATATYPE of the timestamp column?

i’m sorry, i don’t do php

i was actually asking about your ORDER BY clause

Well currently all I have is the timestamp that contains the time and date the post was submitted.

I want to know how I can divide the NET total by the timestamp.

“the number of minutes the post has been active”

where is this information coming from?

I am using the timestamp along with a php script to display how many minutes old a post is.

$todaydate = date("Y-m-d H:i:s");
$ago = strtotime($todaydate) - strtotime($row['timestamp']);
if ($ago >= 86400) {
$diff = floor($ago/86400).' Days ago';
} elseif ($ago >= 3600) {
$diff = floor($ago/3600).' Hours ago';
} elseif ($ago >= 60) {
$diff = floor($ago/60).' Minutes ago';
} else {
$diff = $ago.' Seconds ago';
$diff2 = floor($ago/60).' Minutes ago';


The datatype is ‘timestamp’

    BY (points_up - points_down) / 
         ( ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(timestamp) / 60 ) 

Are you missing a closing parenthesis? I count two opening and one closing on the timestamps…

Should it be…

    BY (points_up - points_down) / 
         ( ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(timestamp)) / 60 ) 


nicely spotted, thanx :slight_smile: