paging php mssql using next prev button

[cecep]

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

[b1randon]

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.

[ronverdonk]

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:

[Atli]

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:

"... LIMIT ". $pageNr * $numRows .", {$numRows}"

wich would give you (e.g. 50 rows per page at page 5)

"... LIMIT 250, 50"

[mrroy44]

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.

<?php
 
$pageNum = 1; 
if (empty($page)){
         $page=1;
     }
     else{
         $page = (int) $page;
     }
 
if(isset($_GET['page'])) 
{ 
    $pageNum = $_GET['page']; 
} 
 
if(!isset($_GET['category_id'])){
 
}
else{
    $id = (int) $_GET['category_id'];
}
 
$offset = ($pageNum - 1) * $rowsPerPage; 
 
 
 
$query  = "SELECT id FROM fotos WHERE session_id='$id' LIMIT $offset, $rowsPerPage"; 
$result = mysql_query($query) or die('Error, query failed'); 
 
 
 
 
// how many rows we have in database 
$query   = "SELECT COUNT(id) AS numrows FROM fotos WHERE session_id='$id'"; 
$result  = mysql_query($query) or die('Error, query failed'); 
$row     = mysql_fetch_array($result, MYSQL_ASSOC); 
$numrows = $row['numrows']; 
 
// how many pages we have when using paging? 
$maxPage = ceil($numrows/$rowsPerPage); 
$offset_prev = (($pageNum - 1) * $rowsPerPage)- $rowsPerPage;
$offset_next = (($pageNum + 1) * $rowsPerPage)- $rowsPerPage;  
 
 
 
// print the link to access each page 
$self = $_SERVER['PHP_SELF']; 
$nav = ''; 
for($page = 1; $page <= $maxPage; $page++) 
{ 
    if ($page == $pageNum) 
    { 
        $nav .= " $page ";   // no need to create a link to current page 
    } 
    else 
    { 
    $offset_nav = ($page * $rowsPerPage) - $rowsPerPage;
        $nav .= " <a href=\"$self?page=$page&category_id=$id&offset=$offset_nav\">$page</a> "; 
    }         
} 
 
// creating previous and next link 
// plus the link to go straight to 
// the first and last page 
 
if ($pageNum > 1) 
{ 
    $page = $pageNum - 1;
    $prev = " <a href=\"$self?page=$page&category_id=$id&offset=$offset_prev\">[Prev]</a> "; 
 
    $first = " <a href=\"$self?page=1&category_id=$id&offset=0\">[First Page]</a> "; 
}  
else 
{ 
    $prev  = '&nbsp;'; // we're on page one, don't print previous link 
    $first = '&nbsp;'; // nor the first page link 
} 
 
if ($pageNum < $maxPage) 
{ 
    $offset_max = ($maxPage * $rowsPerPage) - $rowsPerPage; 
    $page = $pageNum + 1; 
    $next = " <a href=\"$self?page=$page&category_id=$id&offset=$offset_next\">[Next]</a> "; 
 
    $last = " <a href=\"$self?page=$maxPage&category_id=$id&offset=$offset_max\">[Last Page]</a> "; 
}  
else 
{ 
    $next = '&nbsp;'; // we're on the last page, don't print next link 
    $last = '&nbsp;'; // nor the last page link 
} 
 
// print the navigation link 
echo $first . $prev . $nav . $next . $last; 
 
 
// and close the database connection 
include '../library/closedb.php'; 
?>

[riquelme122002]

@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 :

function get_list($offset){
$sql = "
select top (20) from (
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
)
";
$results = $this->db->query($sql);
return $results->result_array();
}