SQL - 在WHERE条件中使用数学运算

Question

I currently have this PHP/SQL code :

$today = date("Y-m-d");

$sqlQuery = 'SELECT id FROM post 
            WHERE (100*((agree+disagree)-('.nbDays('date', $today).')*10) >= 75)';

And my nbDays function.

function nbDays($debut, $fin) {
    $nbSecondes= 60*60*24;

    $debut_ts = strtotime($debut);
    $fin_ts = strtotime($fin);
    $diff = $fin_ts - $debut_ts;
    return round($diff / $nbSecondes);
}

Here is what my post table looks like :

Post
-- #id
-- [int] agree
-- [int] disagree
-- [datetime] date

What i want to do is return an array of ids for the post that match this condition;

(100 * ( agree/ (agree+disagree) )-(number of days from post date to today) ) > 75

I want to use an SQL request so that I don't have to list all posts before sorting them out.

I hope I'm clear enough, and wish you could help me.

Thanks a lot!

Answer 1

In your case you can write a database stored procedure to process your logic . then call database procedure directly in php . In short if you used MYSQL maybe you can try following simple query.

SELECT id FROM post WHERE (100*((agree+disagree)-(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(date))*10) >= 75);