MySQL syntax error

Question

The SQL statement is

mysql_query("update mytable 
    set duration=floor(min(max(TIME_TO_SEC(TIMEDIFF(NOW(), moment))/60,5),600)) 
    where taskid='$taskid' and memberid='$memberid'")or die(mysql_error());

and the error message is

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5),600)) where taskid='4' and memberid='5'' at line 1

Answer 1

Is the "moment" part of your query ok, in the call to TIMEDIFF ?

TIMEDIFF(NOW(), moment)

What is this "moment" ? Should it not be some kind of variable, passed from PHP ?

Thinking a bit more about it : are you sure the functions min, max, and floor exist in MySQL ?

Because trying to call one of those gives me the same error you have :

mysql> select max(5, 10);
ERROR 1064 (42000): You have an error in your SQL syntax; 
   check the manual that corresponds to your MySQL server version 
   for the right syntax to use near ' 10)' at line 1

Or, at least : are you sure they can be used this way, without any group by or the like ?

Answer 2

One big problem (although possibly not the only problem) is that you're using the MIN and MAX aggregate functions incorrectly. In PHP these take an array of values, but in SQL they are aggregate functions, meaning they aggregate values of a result set.

You can read more about aggregate functions in MySQL in the docs at http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Answer 3

Min, Max are select GROUP aggregate functions. use Greatest or Least in this case. And yes, fix 'moment'.

Answer 4

MAX does not work the way you are trying to use. It is an aggregate function that takes one parameter, not two. To select a maximum from two numbers, try this alternative syntax:

SELECT CASE WHEN a > b THEN a ELSE b

Likewise, minimum of two numbers can be evaluated as:

SELECT CASE WHEN a < b THEN a ELSE b

a and b can be column names or expressions.

I assume moment is a date/time column so it should be OK.