SQL LIMIT syntax error

Question

So here is the whole thing:

SELECT * FROM sometable LIMIT 5*DATEDIFF(NOW(), '2011-08-30'), 5

Error:

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 '*DATEDIFF(NOW(), '2011-08-30'), 5' at line 1

The problem is clearly that LIMIT does not accept anything but integer apparently. It won't accept 1+1 or (1+1) as value either. Is there a way around it?

And just so you don't have to go and try, 5*DATEDIFF(NOW(), '2011-08-30') works just fine.

I am trying to fix this answer..

Answer 1

NO, is not doable in plain mysql
(however, is possible via stored procedure, user defined function)

your case, it can easily replaced via a PHP call

$offset = 5*
        date_diff(new DateTime('now'), new DateTime('2011-08-31'))->format('%a');
$sql = "SELECT * FROM sometable LIMIT {$offset},5";

Answer 2

The limit must be an integer or local variable. From the MySQL docs:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.