Recipe 3.18. Calculating LIMIT Values from Expressions


Problem

You want to use expressions to specify the arguments for LIMIT.

Solution

Sadly, you cannot. You can use only literal integersunless you issue the statement from within a program, in which case you can evaluate the expressions yourself and insert the resulting values into the statement string.

Discussion

Arguments to LIMIT must be literal integers, not expressions. Statements such as the following are illegal:

SELECT * FROM profile LIMIT 5+5; SELECT * FROM profile LIMIT @skip_count, @show_count; 

The same "no expressions allowed" principle applies if you're using an expression to calculate a LIMIT value in a program that constructs a statement string. You must evaluate the expression first, and then place the resulting value in the statement. For example, if you produce a statement string in Perl or PHP as follows, an error will result when you attempt to execute the statement:

$str = "SELECT * FROM profile LIMIT $x + $y"; 

To avoid the problem, evaluate the expression first:

$z = $x + $y; $str = "SELECT * FROM profile LIMIT $z"; 

Or do this (but don't omit the parentheses or the expression won't evaluate properly):

$str = "SELECT * FROM profile LIMIT " . ($x + $y); 

If you're constructing a two-argument LIMIT clause, evaluate both expressions before placing them into the statement string.




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net