Pagination

[Jfury]

Hello everyone, I currently have a script here and it's working perfectly fine - I was wondernig if someone can help me on how to reverse the order the output displays (I want the most recent entires to display rather then the oldest first.)

<?php
//connecting to the database using ODBC
include("../Intranet-Template1/db/connection.php");
/******************************************************************************/
$limit = 5;  

$query_count    = "SELECT COUNT(*) as value FROM test";
$result_count   = odbc_exec($connect, $query_count); 
$totalrows  = odbc_fetch_object($result_count);   

if(empty($page)){    // Checks if the $page variable is empty (not set)
        $page = 1;      // If it is empty, we're on page 1
    }

$limitvalue = $page * $limit - ($limit);
$limitnew = $limitvalue + $limit; 
$query = "SELECT * FROM test WHERE testid BETWEEN $limitvalue AND $limitnew ORDER BY testid DESC";

$result = odbc_exec($connect, $query);
while (odbc_fetch_array($result)) {

$test1 = odbc_result($result, 'testid'); 
echo $test1."<br>";

} 
  if($page != 1){
        $pageprev = $page -1;
		 echo("&nbsp;<b><a href=test2.php?page=$pageprev>PREV</a></b>&nbsp;");
    }else{
        echo("&nbsp;PREV&nbsp;");
    }
   $numofpages = $totalrows->value / $limit;
    for($i = 1; $i <= $numofpages; ++$i){
        if($i == $page){
            echo("&nbsp;[$i]&nbsp;");  

        }else{
            echo("&nbsp;<b><a href=test2.php?page=$i>$i</a></b>&nbsp;");
        }
    }
    if(($totalrows->increm % $limit) != 0){
        if($i == $page){
            echo("&nbsp;[$i]&nbsp;");
        }else{
            echo("&nbsp;<b><a href=test2.php?page=$i>$i</a></b>&nbsp;");
        }
    }
if(($totalrows->value - ($limit * $page)) > 0){
         $pagenext = $page +1;

    echo("&nbsp;<b><a href=test2.php?page=$pagenext>NEXT</a></b> ");
}else{
    echo("&nbsp;NEXT&nbsp;");
} 
odbc_free_result($result);

?> 

Thanks in advance and any help will be greatly appreciated!

• J

[notaloser]

$query = "SELECT * FROM test WHERE testid BETWEEN $limitvalue AND $limitnew ORDER BY date ASC";

is that what you mean? you want the newest entries to be displayed first?

[Jfury]

It's currently being displayed like this:

PAGE 1

ID:

5
4
3
2
1

PREV 1, 2, 3 NEXT

PAGE 2

ID:

9
8
7
6
5

PREV 1, 2, 3 NEXT

============================

I want it to display like this:

PAGE 1

ID:

9
8
7
6
5

PREV 1, 2, 3 NEXT

PAGE 2

ID:

5
4
3
2
1

PREV 1, 2, 3 NEXT

I'm just kinda tired to this on my own and hopefully someone can help me out here. 🙂

[anandm78]

I did something pretty similar where I had users posting comments on pictures. I wanted to display the most recent one on top in the first page and then onwards in descending order. Here is what I did -

In my code, page number start from 1. The table's index is called commentid which is auto incremented.

define (MAX_COMMENTS_IN_PAGE, 10);

$query = "SELECT count(commentid) FROM commentstable";
// now execute the query - I used MySQL.

$numComments = // set your query result from above;

/* get the number of pages by dividing number of comments by the max number of comments in a page */
$numPages = (Integer)($numComments / MAX_COMMENTS_IN_PAGE);

/* interesting case where the above division leaves a remainder.  For example, you have 11 comments, then the above division will yield 1 as the result, but then you need 2 pages with the second page containing the last comment.  You can also do this nicely in the division result in the previous statement itself by using the ceil() function.  Anyways ....*/

if( ($numComments % MAX_COMMENTS_IN_PAGE) > 0 )
	$numPages++;

/* this is just a check to make sure we have the right page number. $page is coming from the GET request */
if( $page > $numPages )
	$page = 1;

$startLimit = ($page - 1) * MAX_COMMENTS_IN_PAGE;
$endLimit = MAX_COMMENTS_IN_PAGE;

/* this should return values from the most recent up to the limit for the page */
$query = "SELECT * FROM commentstable ORDER BY commentid DESC LIMIT $startLimit, $endLimit";

/* output html - blah blah blah */

The problem with using the BETWEEN is that it assumes that the rowids are continuous. If a row is deleted, it'll have problems. So I thought, using the LIMIT is probably the best way.

I'm a novice in SQL, so this is as far as I could get. If there is a better solution, I'd love to see and incorporate!

Thanks ...

[Jfury]

Thanks for the reply, this would be much easier if I was using a different database; however, i'm currently using ms server and LIMIT clause has been removed.

[Velausanakha]

JFury, are you sure this works?

I've used a modified version of your work crossed with something similar from zend.

The increm bit goes over my head as does the way some of the page numbering is allocated but I find when you click next or the page 2 or other links that it changes the url but it doesn't update the data displayed.

[Jfury]

Hey Vel,

This was also taken from zend and yes it does work - but I haven't solved the problem yet.

[Velausanakha]

hmm, any idea what my problem could be then?

Data on page one turns out fine, click for page 2 and the data remains the same but the URI changes.

It does work though when I change the page number given in this code

if(empty($page)){    // Checks if the $page variable is empty (not set)
        $page = 1;      // If it is empty, we're on page 1
    }