PHP RSS PHP RSS

Pagination Without Limit in Query

Reply
1 2 3

•
•
Join Date: Nov 2008
Posts: 14
Reputation: soldierflup is an unknown quantity at this point 
Solved Threads: 0
soldierflup's Avatar
soldierflup soldierflup is offline Offline
Newbie Poster
 
0
  #11
29 Days Ago
Take a look at the next page, it might be a solution for your problem.

http://positionabsolute.net/blog/200...pagination.php
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,475
Reputation: cwarn23 has a spectacular aura about cwarn23 has a spectacular aura about cwarn23 has a spectacular aura about 
Solved Threads: 136
cwarn23's Avatar
cwarn23 cwarn23 is offline Offline
Nearly a Posting Virtuoso
 
0
  #12
29 Days Ago
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 Syntax (Toggle Plain Text) 
  1. <?php
  2. //some array called $arr created above
  3. for ($i=$start;$i<=$end;$i++) {
  4.     //regular pegination code in here.
  5.     //below is an example
  6.     echo "<a href=\"index.php?page=$i\">$i</a><br>";
  7.     }
  8. echo $arr[$_GET['page']];
Try not to bump 10 year old threads as it can be really annoying.
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 108
Reputation: sam023 is an unknown quantity at this point 
Solved Threads: 2
sam023 sam023 is offline Offline
Junior Poster
 
0
  #13
29 Days Ago
hmmm i think u r right it is bad idea to use array..!!!

Well anyone know how to use limit in store procedure..?
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 108
Reputation: sam023 is an unknown quantity at this point 
Solved Threads: 2
sam023 sam023 is offline Offline
Junior Poster
 
0
  #14
28 Days Ago
i dont think we can use limit function directly in store procedures..!
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2009
Posts: 527
Reputation: network18 is an unknown quantity at this point 
Solved Threads: 61
network18 network18 is offline Offline
Posting Pro
 
0
  #15
28 Days Ago
•
•
•
•
Originally Posted by sam023 View Post
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 -
PHP Syntax (Toggle Plain Text) 
  1. DELIMITER $$
  2.  
  3. DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$
  4.  
  5. CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),page_size int(2),row_start int(2),country_code char(30))
  6. BEGIN
  7. if(country_code="") then
  8. select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
  9. cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc limit row_start,page_size;
  10. else
  11. select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
  12. 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;
  13. end if ;
  14.     END$$
  15. 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.
"The discipline of writing something down is the first step towards making it happen."

follow me on twitter
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 108
Reputation: sam023 is an unknown quantity at this point 
Solved Threads: 2
sam023 sam023 is offline Offline
Junior Poster
 
0
  #16
28 Days Ago
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2009
Posts: 527
Reputation: network18 is an unknown quantity at this point 
Solved Threads: 61
network18 network18 is offline Offline
Posting Pro
 
0
  #17
28 Days Ago
•
•
•
•
Originally Posted by sam023 View Post
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-
PHP Syntax (Toggle Plain Text) 
  1. DELIMITER $$
  2.  
  3. DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$
  4.  
  5.  
  6. CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),page_size int(2),row_start int(2),country_code char(30))
  7. BEGIN
  8. SET @lim = CONCAT(' LIMIT ', LimitStart_, ',', LimitCnt_);
  9.  
  10. if(country_code="") then
  11. 	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
  12. 	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc";
  13. else
  14. 	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
  15. 	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc";
  16.  
  17. end if ;
  18.  
  19. SET @q = CONCAT(@q, @lim);
  20. PREPARE st FROM @q;
  21. EXECUTE st;
  22. DEALLOCATE PREPARE st;
  23.     END$$
  24. 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.
Last edited by network18; 28 Days Ago at 2:43 am. Reason: more information about the MySQL bug
"The discipline of writing something down is the first step towards making it happen."

follow me on twitter
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 108
Reputation: sam023 is an unknown quantity at this point 
Solved Threads: 2
sam023 sam023 is offline Offline
Junior Poster
 
0
  #18
15 Days Ago
•
•
•
•
Originally Posted by network18 View Post
yes, thank you so much for that and check out the below modification-
PHP Syntax (Toggle Plain Text) 
  1. DELIMITER $$
  2.  
  3. DROP PROCEDURE IF EXISTS `dd`.`view_ratecard`$$
  4.  
  5.  
  6. CREATE DEFINER=`root`@`localhost` PROCEDURE `view_ratecard`(id_tariffplan int(11),page_size int(2),row_start int(2),country_code char(30))
  7. BEGIN
  8. SET @lim = CONCAT(' LIMIT ', LimitStart_, ',', LimitCnt_);
  9.  
  10. if(country_code="") then
  11. 	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
  12. 	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' ORDER by destination asc";
  13. else
  14. 	SET @q = "select dialprefix,destination,rateinitial,initblock,billingblock,id_trunk,grace,idtariffplan,id from
  15. 	cc_ratecard where idtariffplan=id_tariffplan and rate_version='0' and dialprefix like concat(country_code,'%') ORDER by destination asc";
  16.  
  17. end if ;
  18.  
  19. SET @q = CONCAT(@q, @lim);
  20. PREPARE st FROM @q;
  21. EXECUTE st;
  22. DEALLOCATE PREPARE st;
  23.     END$$
  24. 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'
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2009
Posts: 527
Reputation: network18 is an unknown quantity at this point 
Solved Threads: 61
network18 network18 is offline Offline
Posting Pro
 
0
  #19
10 Days Ago
•
•
•
•
Originally Posted by sam023 View Post
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 -
PHP Syntax (Toggle Plain Text) 
  1. 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.
"The discipline of writing something down is the first step towards making it happen."

follow me on twitter
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 108
Reputation: sam023 is an unknown quantity at this point 
Solved Threads: 2
sam023 sam023 is offline Offline
Junior Poster
 
0
  #20
10 Days Ago
Now it throughing this error..

Unknown column 'id_tariffplan' in 'where clause'
Reply With Quote Quick reply to this message  
Reply
1 2 3

Quick Reply to Thread
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the PHP Forum
Thread Tools Search this Thread



apache api array beginner beneath binary broadband broken button cakephp checkbox class cms code countingeverycharactersfromastring crack cron curl data database date decode display dynamic echo email error file files folder form forms function functions google href htaccess html image include insert integration ip javascript joomla limit link links login mail match md5 menu mlm multiple mysql mysql_real_escape_string oop paypal pdf php problem protocol query radio random recursion remote script search searchbox server session sessions sms smtp soap source space sql strip_tags survey syntax system table tutorial undefined update upload url validator variable video virus votedown web website window.onbeforeunload=closeme; xml youtube
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC