Get page wise records in mysql

Get page wise records

Select * from items Limit 0,10            >> return firt 10 records
Select * from items Limit 11,10          >> return 10 records starts from 11

DELIMITER $

DROP PROCEDURE IF EXISTS `demo_sp_simplePaging` $

CREATE PROCEDURE `demo_sp_simplePaging` (

p_searchPhrase VARCHAR(50),

p_page_no INT

)

BEGIN



DECLARE int_page_size INT;

DECLARE int_last_page INT;



SET int_page_size = 20;



SET p_page_no = COALESCE(p_page_no, 1);



DROP TEMPORARY TABLE IF EXISTS `my_table_rows`;



CREATE TEMPORARY TABLE `my_table_rows` (

`tmp_idx` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`tmp_id` INT NOT NULL

)ENGINE=memory;



INSERT INTO `my_table_rows`(`tmp_id`)

SELECT `my_key_id`

FROM `my_table`

WHERE `filter_col` LIKE p_searchPhrase

ORDER BY `order_col`;



SELECT max(CEILING(`tmp_idx`/int_page_size))

INTO int_last_page

FROM `my_table_rows`;



#prev page no

IF p_page_no > 1 THEN



SELECT p_page_no - 1 AS "PREV_PAGE";



END IF;



#next page no

IF p_page_no < int_last_page THEN



SELECT p_page_no + 1 AS "NEXT_PAGE";



END IF;



SELECT *

FROM `my_table_rows` JOIN `my_table` ON `my_key_id` = `tmp_id`

WHERE CEILING(`tmp_idx`/int_page_size) = p_page_no

ORDER BY `tmp_idx`;



DROP TEMPORARY TABLE IF EXISTS `my_table_rows`;



END$

DELIMITER;