Pagination Without Limit in Query

[sam023]

is there any way to paginate the page without using limit in Query..!!
Actually i m working on predefined stored procedures. and i m not allowed to make in changes that..!!! and that does contain limit function at all..!!!!

any suggestions how should i do it.?

thanks in advance

[Will Gresham]

Surely pagination without a limit would not be pagination...

The limit is there to specify how many results are retreived, without it all records would be pulled on each query.

[network18]

look around for using SQL_CALC_FOUND_ROWS in query, it may help you

[sam023]

hmmmm... how about this logic.. taking whole data set in an array..!! and apply pagination function on it.!!

is this possible..? :O

[network18]

so whats the need to do this, anyways you fetching the entire data and storing it to some array, it will consume lots and lots of memory.Better to use the MySQL query with the limit.
You can cache the result coming each time for some predefined time duration.It will help to make the results faster.

[sam023]

if i can use limit in query then surely i will not create this thread..!!
anyways thanks for reply

[network18]

why you cant use the limit in query, just post your code and the problem it creates when used the limit.
I am sure there is some misconception about some patch of code

[sam023]

DELIMITER $$

DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),country_code char(30))
BEGIN
if(country_code="") then
select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc;
else
select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc;
end if ;
    END$$
DELIMITER ;

its a simple store procedure..!!! which returns more than 150 records..!! the same procedure is used by our .Net department..!! and they dont need limit in Query at all..!

[network18]

It is always nice to have one SP against such queries which get fired often.Ok, they told you to implement the same SP as it is and you are worried about the pagination.You can proceed the way you want like gettign everything in entire array, if you are sure that, the rows returned by that SP are not much.
Or else you can modify the same simple SP to accept the start index and the page_size for making pagination easier.

[makgaboe]

An array will do for now. But you will need to use limit at some point

Take a look at the next page, it might be a solution for your problem.

http://positionabsolute.net/blog/2007/10/prototype-pagination.php

[cwarn23]

I shall put it plain and simple. While it is easily possible to loop through part of an array instead of the mysql result with pegination, an array with a trillion results will crash the server just like a reverse hash lookup table. Imagine storing a trillion results in an array and only looping through the required section - not possible unless you had a super-computer with a Petabyte of ram. Anyways, if you still are convinced you want to loop through an array then the following is an example of how.

<?php
//some array called $arr created above
for ($i=$start;$i<=$end;$i++) {
    //regular pegination code in here.
    //below is an example
    echo "<a href=\"index.php?page=$i\">$i</a><br>";
    }
echo $arr[$_GET['page']];

[sam023]

hmmm i think u r right it is bad idea to use array..!!!

Well anyone know how to use limit in store procedure..?

[sam023]

i dont think we can use limit function directly in store procedures..!

[network18]

i dont think we can use limit function directly in store procedures..!

Well, limit is not the function at first place, and MySQL recognizes 'limit', so its pretty possible if you modify the SP somewhat like below -

DELIMITER $$

DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),page_size int(2),row_start int(2),country_code char(30))
BEGIN
if(country_code="") then
select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc limit row_start,page_size;
else
select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc limit row_start,page_size;
end if ;
    END$$
DELIMITER ;

Each time you call it, just pass the "row_start" parameter to it, along with the other parameters, which you can manipulate with the PHP. The "page_size" will be fixed for you like $page_size = 10
I hope you got this concept.

[sam023]

its not that simple.

for passing dynamic values in limit. i think we need prepare statement..!!

else it will throw error..!!!

u can try this

[network18]

its not that simple.

for passing dynamic values in limit. i think we need prepare statement..!!

else it will throw error..!!!

u can try this

yes, thank you so much for that and check out the below modification-

DELIMITER $$

DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$


CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),page_size int(2),row_start int(2),country_code char(30))
BEGIN
SET @lim = CONCAT(' LIMIT ', LimitStart_, ',', LimitCnt_);

if(country_code="") then
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc";
else
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc";

end if ;

SET @q = CONCAT(@q, @lim);
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
    END$$
DELIMITER ;

post if still there is any modification.

Edit: Its a bug actually, you will like to have a look at this for more information.

[sam023]

yes, thank you so much for that and check out the below modification-

DELIMITER $$

DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$


CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),page_size int(2),row_start int(2),country_code char(30))
BEGIN
SET @lim = CONCAT(' LIMIT ', LimitStart_, ',', LimitCnt_);

if(country_code="") then
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc";
else
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc";

end if ;

SET @q = CONCAT(@q, @lim);
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
    END$$
DELIMITER ;

post if still there is any modification.

Edit: Its a bug actually, you will like to have a look at this for more information.

When i call the procedure.. it give this error

Unknown column 'LimitStart_' in 'field list'

[network18]

When i call the procedure.. it give this error

Unknown column 'LimitStart_' in 'field list'

Its very obvious that, it should have been like this -

SET @lim = CONCAT(' LIMIT ', row_start, ',', page_size);

those were just variables passed to your stored procedure, to set the 'LIMIT' part in the query.

[sam023]

Now it throughing this error..

Unknown column 'id_tariffplan' in 'where clause'

[cwarn23]

Now it throughing this error..

Unknown column 'id_tariffplan' in 'where clause'

That is a basic mysql error which means mysql has been told to search for a column named id_tariffplan however no such column exists. A common problem for this is mis-spelling the column and not having the column inserted in the first place.

[network18]

Now it throughing this error..

Unknown column 'id_tariffplan' in 'where clause'

are you passing that variable at the time of calling.
Post it here, how you calling the procedure from php

[network18]

id_tariffplan is the variable to hold the first parameter value passed to the stored procedure.
So its not a column name.Either the error is "unknown column idtariffplan" , (where idtariffplan is the column name) and id_tariffplan will just assign its value to it in the where clause.

[sam023]

DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),page_size int(2),row_start int(2),country_code char(30))
BEGIN
SET @lim = CONCAT(' LIMIT ', row_start, ',', page_size);

if(country_code="") then
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc";
else
	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc";

end if ;

SET @q = CONCAT(@q, @lim);
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
    END$$
DELIMITER ;
end if ;

This is your Store procedure..

i m just calling in database by call function

call view_ratecard_php('1','10','0','')

and it throughing the error

[sam023]

id_tariffplan is the variable to hold the first parameter value passed to the stored procedure.
So its not a column name.Either the error is "unknown column idtariffplan" , (where idtariffplan is the column name) and id_tariffplan will just assign its value to it in the where clause.

Exactly...

id_tariffplan is variable and idtraiffplan is column name..!!! but still i m getting this error

Unknown column 'id_tariffplan' in 'where clause'

i think it is because here you are writing query in string format..!! by string format i mean query in double quotes""... then how can u pass variable in it..?

i think for passing variable in string we need '?'

i have already a SP for limit..! but i just to try your SP..!!

[network18]

No.MySQL throws this error , when we try to specify a column name not present in the table structure.
So make sure the table structure is fine

[sam023]

No.MySQL throws this error , when we try to specify a column name not present in the table structure.
So make sure the table structure is fine

select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from

cc_ratecard where idtariffplan=id_tariffplan

Thats what i m saying.. Mysql taking id_tariffplan as column not as variable.. and there is no column name as id_tariffplan...thats why it is throwing that error..!! may be because it in string format..!!!

even if u run above query out of SP(directly).. it will throw same error..!!!

[sam023]

i hope now getting me..!