Pagination variable

Question

I have a pagination code.

$nextpage = $pageno+1;

This creates a html link for the next page. Unfortunately the $pageno variable, lets say it is 350 for example doesn't work by a +1 change. That would take it too the id 351, and the rows in the database aren't always continuous numbers, they have gaps.

1, 2, 4, 6, 7 etc (this is because of the way the script assigns an id.

So instead of having a +1 pagination variable, how can I tell it to find the next row instead?

Thanks

EDIT:

I found the solution in this thread: mysql get previous and next record id

Use a `LIMIT` clause in your sql, that can be (is...) completely unrelated to your ID numbers. — jeroen, May 28 '13 at 21:42

score 0 · Answer 1 · answered May 28 '13 at 22:05

The primary key for your DB table should be auto_incriment. But deleting rows can still leave holes in the IDs.

As @jeroen metioned you should not be selecting rows by IDs for lists. Just use LIMIT.

example:

SELECT * FROM post WHERE active = 1 ORDER BY id LIMIT 0, 10

score 0 · Answer 2 · answered May 28 '13 at 22:05

Heres a really simple example :

For the page, we simply use the querystring. In this example, let us use $_GET['page'] to initiallize the $page variable. If the GET var isn't set we default to page 1.

$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;

Now, we want to figure out how many pages are in our query. For this, we can use the COUNT() SQL function, then divide the number returned by the amount of results per page we want.

$pages = implode(mysql_fetch_assoc(mysql_query("SELECT COUNT(key) FROM table")));
$pages = ceil($pages / 6);

Now, because we may have other GET variables set we want to preserve the querystring. To do this, we just assemble all the current variables (except the page one, of course) into a string.

$querystring = "";
foreach ($_GET as $key => $value) {
    if ($key != "page") $querystring .= "$key=$value&amp;";
}

Then, we can loop through the pages, and echo out the links to them. Using a conditional or ternary operator we can also assign a different class to the current page.

echo "Pages: ";
for ($i = 1; $i <= $pages; $i++) {
    echo "<a " . ($i == $page ? "class=\"selected\" " : "");
    echo "href=\"?{$querystring}page=$i";
    echo "\">$i</a> ";
}

Then, to display only the results that are relevant to the current page we can use the LIMIT SQL clause, or in this case LIMIT (($page - 1) * 6), 6 (because we have 6 results per page, and the first page needs to query from the 0th record).

$result = mysql_query("SELECT * FROM table LIMIT " . (($page - 1) * 6) . ", 6");

I've changed the code around and it displays all rows as page numbers. So an album containing 80 rows shows pages: 1 - 80. The problem where you have the `$i` variable, denoting a page number. The original problem is still present. It doesn't call the next existing row, just presumes the id's are 1-80. — Seth-77, May 29 '13 at 10:10

Pagination variable

2 Answers2