CODEWITHSUNDEEP
phpmysqlstored-procedurespagination
chaitanyasingu
chaitanyasingu

Reputation: 141

creating pagination with mysql stored procedure

M trying to create pagination with stored procedure,

when I try to pass 1 as param it get record 0 to 14.
and when I pass 2 as param it gives 0-30, I want 15-30 when I pass 2 as param

create PROCEDURE abc(pageno INT)
BEGIN  
DECLARE page_no int;
DECLARE rstarts int;
DECLARE rends int; 
DECLARE userPage int;

set @userPage = pageno;

PREPARE STMT FROM 
" SELECT * FROM primaryinfo LIMIT ?,? "; 
SET @START =(@userPage - 1) * 15; 
SET @LIMIT = @userPage * 15; 
EXECUTE STMT USING @START, @LIMIT;
DEALLOCATE PREPARE STMT;
END

or is there any other solution to do it effectively.. Thank you in advance

Upvotes: 2

Views: 2921

Answers (1)

Axe
Axe

Reputation: 29

pagination without statements:

create PROCEDURE test(
  IN first_rec integer,
  IN rec_count integer
)
BEGIN
  -- return --
  SET @rownum=0;
  SELECT * FROM (
    SELECT
    user.*, @rownum:=@rownum+1 AS rn FROM user
  ) t WHERE rn>=first_rec and rn<first_rec+rec_count;
END;;

Upvotes: 2

Related Questions