CODEWITHSUNDEEP
mysqlsql
Chen
Chen

Reputation: 3060

order by with parameters in sql

I'm trying to use variables in mysql query:

SET @resultsPerPage = 10;
SET @offset = 0;

and in query:

ORDER BY u.USER_NAME ASC LIMIT @offset, @resultsPerPage

the Error I receive is :

Error Code: 1064. You have an error in your SQL syntax;

any suggestions?

Upvotes: 0

Views: 81

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

A possible workaround for achieving pagination is using variables in the following way:

SET @resultsPerPage = 10; -- Display 10 records per page
SET @offset = 0;          -- Display the first page

SELECT *
FROM (
   SELECT @rn := @rn + 1 AS rn, u.*
   FROM mytable AS u
   CROSS JOIN (SELECT @rn := 0)
   ORDER BY u.USER_NAME) AS t
WHERE t.rn >= @offset * @resultsPerPage + 1 AND
      t.rn <= (@offset + 1) * @resultsPerPage

Upvotes: 4

Related Questions