469,353 Members | 2,053 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,353 developers. It's quick & easy.

paging php mssql using next prev button

1
hei, anyone! i need help to paging php using mssql, and php script in the program to make a next and previous button.. please sending message for me if you can answer my question
Dec 13 '06 #1
5 14328
b1randon
171 Expert 100+
hei, anyone! i need help to paging php using mssql, and php script in the program to make a next and previous button.. please sending message for me if you can answer my question
Why don't you post what you have so far? It would help. To get you started, paging is really easy if you use mysql's LIMIT keyword. You can limit from row x to row y. A query is really easy b/c you can dynamically generate a LIMIT statement with something like LIMIT (50*x), (50*(x+1)) where x is your page number. That syntax isn't right for sure but you get the idea. It will give you 50 results per page ( page 0 has results 0-50, 1 has 50-100 etc). To find the total number of pages just do a query for count(*) /50. Post some code and I can help further.
Dec 14 '06 #2
ronverdonk
4,258 Expert 4TB
When you google for terms like 'pagination results display' you will get many replies pointing to code snippets that can perform what you are looking for.

Ronald :cool:
Dec 24 '06 #3
Atli
5,058 Expert 4TB
Just a note...

The MySQL LIMIT clause doesnt work exectly like you say. The first parameter is the start index, the second is the number of rows to return:

... LIMIT start, ammount

So dynamicly createing a limit clause in php would go something like this:

Expand|Select|Wrap|Line Numbers
  1. "... LIMIT ". $pageNr * $numRows .", {$numRows}"
wich would give you (e.g. 50 rows per page at page 5)

Expand|Select|Wrap|Line Numbers
  1. "... LIMIT 250, 50"
Dec 26 '06 #4
Hi There,

I in fact got a paging system ready to use now. it works great. Maybe it is something you are looking for.

But i also got a question to improve it.

As a result, when i got about 13 pages to show it will show you the following:

[First Page][Prev][1][2][3][4][5][6][7][8][9][10][11][12][13][Next][Last Page]

But i would realy like it to show a limit of 10 pages at a time, so when i get to page 7 or at least further then page 2 it shows

[First Page][Prev][3][4][5][6][7][8][9][10][11][12][Next][Last Page]

Does anyone know how to do that? I'm not really a very good with php (and dutch;) so please do not get to technical on this.


Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. $pageNum = 1; 
  4. if (empty($page)){
  5.          $page=1;
  6.      }
  7.      else{
  8.          $page = (int) $page;
  9.      }
  10.  
  11. if(isset($_GET['page'])) 
  12.     $pageNum = $_GET['page']; 
  13.  
  14. if(!isset($_GET['category_id'])){
  15.  
  16. }
  17. else{
  18.     $id = (int) $_GET['category_id'];
  19. }
  20.  
  21. $offset = ($pageNum - 1) * $rowsPerPage; 
  22.  
  23.  
  24.  
  25. $query  = "SELECT id FROM fotos WHERE session_id='$id' LIMIT $offset, $rowsPerPage"; 
  26. $result = mysql_query($query) or die('Error, query failed'); 
  27.  
  28.  
  29.  
  30.  
  31. // how many rows we have in database 
  32. $query   = "SELECT COUNT(id) AS numrows FROM fotos WHERE session_id='$id'"; 
  33. $result  = mysql_query($query) or die('Error, query failed'); 
  34. $row     = mysql_fetch_array($result, MYSQL_ASSOC); 
  35. $numrows = $row['numrows']; 
  36.  
  37. // how many pages we have when using paging? 
  38. $maxPage = ceil($numrows/$rowsPerPage); 
  39. $offset_prev = (($pageNum - 1) * $rowsPerPage)- $rowsPerPage;
  40. $offset_next = (($pageNum + 1) * $rowsPerPage)- $rowsPerPage;  
  41.  
  42.  
  43.  
  44. // print the link to access each page 
  45. $self = $_SERVER['PHP_SELF']; 
  46. $nav = ''; 
  47. for($page = 1; $page <= $maxPage; $page++) 
  48.     if ($page == $pageNum) 
  49.     { 
  50.         $nav .= " $page ";   // no need to create a link to current page 
  51.     } 
  52.     else 
  53.     { 
  54.     $offset_nav = ($page * $rowsPerPage) - $rowsPerPage;
  55.         $nav .= " <a href=\"$self?page=$page&category_id=$id&offset=$offset_nav\">$page</a> "; 
  56.     }         
  57.  
  58. // creating previous and next link 
  59. // plus the link to go straight to 
  60. // the first and last page 
  61.  
  62. if ($pageNum > 1) 
  63.     $page = $pageNum - 1;
  64.     $prev = " <a href=\"$self?page=$page&category_id=$id&offset=$offset_prev\">[Prev]</a> "; 
  65.  
  66.     $first = " <a href=\"$self?page=1&category_id=$id&offset=0\">[First Page]</a> "; 
  67. }  
  68. else 
  69.     $prev  = '&nbsp;'; // we're on page one, don't print previous link 
  70.     $first = '&nbsp;'; // nor the first page link 
  71.  
  72. if ($pageNum < $maxPage) 
  73.     $offset_max = ($maxPage * $rowsPerPage) - $rowsPerPage; 
  74.     $page = $pageNum + 1; 
  75.     $next = " <a href=\"$self?page=$page&category_id=$id&offset=$offset_next\">[Next]</a> "; 
  76.  
  77.     $last = " <a href=\"$self?page=$maxPage&category_id=$id&offset=$offset_max\">[Last Page]</a> "; 
  78. }  
  79. else 
  80.     $next = '&nbsp;'; // we're on the last page, don't print next link 
  81.     $last = '&nbsp;'; // nor the last page link 
  82.  
  83. // print the navigation link 
  84. echo $first . $prev . $nav . $next . $last; 
  85.  
  86.  
  87. // and close the database connection 
  88. include '../library/closedb.php'; 
  89. ?>
Dec 27 '06 #5
@cecep
okay just to add some note.. here

some dude from code igniter forum create a simple of function to make pagination....

i think this function will work great with all database. and you dont havta use code igniter framework.

im currently using php and mssql server 2005 with Code Igniter Framework
and it works well with the function...

just view it here http://codeigniter.com/forums/viewthread/64287/
and this is the query that i used :
Expand|Select|Wrap|Line Numbers
  1. function get_list($offset){
  2. $sql = "
  3. select top (20) from (
  4. select id,name, ROW_NUMBER() over (order by id) as Result_Number from tm_person) innerSel Where Result_Number >(($offset - 1) *20) Order by Result_Number
  5. )
  6. ";
  7. $results = $this->db->query($sql);
  8. return $results->result_array();
  9. }
Feb 19 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Dan Nash | last post: by
1 post views Thread by ratnakarp | last post: by
2 posts views Thread by nse111 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.