I have an error in my syntax :
SET @start := 0;
SELECT (ROUND((count(item))/2)) FROM car INTO @until;
SELECT * FROM car
LIMIT @until OFFSET @start;
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 '@until OFFSET @start' at line 1
Anyone can help me?
Thanks
You cannot use a user-defined variable (@until) in the LIMIT clause.
A possible solution (a variation on this):
SELECT (ROUND((count(item))/2)) FROM car INTO @until;
SELECT * FROM (
SELECT *,
@rownum := @rownum + 1 AS rank
FROM car,
(SELECT @rownum := 0) r
) d WHERE rank < @until;
Only downside is you lose the offset, but you can accommodate that by adjusting the WHERE clause. Otherwise, you can use a stored procedure.
LIMIT. It appears you can't use it there: stackoverflow.com/questions/245180/…. I'll update my answer with a possible solution.You could try something like this:
DELIMITER //
CREATE PROCEDURE pagination(
INT pageno INT
)
BEGIN
DECLARE pages INT;
DECLARE pagefrom INT;
SET pages = 1000; -- get this many records at a time
SET pagefrom = pageno * pages; -- 2 * 1000 = 2000
SELECT *
FROM car
LIMIT pagefrom, pages;
END //
DELIMITER ;
CALL pagination(2);
This example will get records from 2000 to 3000
DECLARE syntax for defining local variables. Local variables declared in such manner can be used with LIMIT. Just remember that DECLARE statements must be written first inside the body of a prepared statement.