My PHP Pagination problem

PHP
1

Okay so I can’t seem to make this pagination work. I mean it works. It shows data like I want it to, but what I’m trying to achieve is allow only up to 20 data to be pulled from the database per page. Well, it does do that, but this is what it also does.

This is the first pagination. So the URL and everything works. The URL is ?page=2. This page is fine since it’s doing what I asked. List #1 data - #20 data.

Now here’s where it’s acting weird on me. It lists 20 datas like I asked, but for the first data of the page, it shows the last data from the previous page. So 20th comment actually shows on ?page=3.

Here, it’s taking 2 datas from the previous page. So from the original ?page=2, it pulled 2 datas into ?page=4 from both ?page=2 and ?page=3

Here’s my code.

$GET_URL_PAG_DATABASE = new mysqli("localhost", "username", "password", "database");
if($GET_URL_PAG_DATABASE->connect_errno) {
    echo "Error, failed to connect to MySQL database. Please fix.";
    exit();
}

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

$pages = implode(mysqli_fetch_assoc($GET_URL_PAG_DATABASE->prepare("SELECT COUNT(id) FROM comment WHERE parent_id = ?")));
$pages = ceil($pages / 20);

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

$GET_URL_PAG = $GET_URL_PAG_DATABASE->prepare("SELECT id, parent_id, message FROM comment WHERE parent_id = ? ORDER BY id ASC LIMIT " . (($page - 1) * 19) . ", 20");
$GET_URL_PAG->bind_param("s", $GET_ID);
$GET_ID = filter_var($_GET['id'], FILTER_SANITIZE_URL);
$GET_URL_PAG->execute();
$GET_URL_PAG->bind_result($GET_COMMENT_ID, $GET_PARENT_ID, $GET_MESSAGE);

while($GET_URL_PAG->fetch()) {
    echo "ID Number: " . $GET_COMMENT_ID . ": " . $GET_MESSAGE . "<br />";
}

I know that it has something to do with " . (($page - 1) * 19) . “, 20” but if I change 19 into 20, it’ll pull only never include the first ever data in ?page=2. So if the message is “1st comment”, it’ll only show “2nd comment” and not “1st comment”. If I change $page - 1 to $page - 2, it do the same thing, but going forwards, if I change it to $page - 0, it won’t show anything.

I need some guidance. Also, sorry if all my variables are in caps, I normally like to keep my stuff in caps so that I know where everything is.

2

I think it should work if you change 19 to 20. For example, you can see like this (just a pseudo):

If ($page  == 1){
    $start = ((1 - 1) * 20);
}

Which means LIMIT $start, 20; Data from 0 to 19 are selected

If ($page == 2){
    $start = ((2 - 1) * 20);
}

20 records starting from 20. Does this make sense or I am understanding your problem something different ?

3

@Raju_Gautam
Hmm, I still see the same thing. I already have an if statement set up for dealing with page 1.

First if statement is

if(isset($_GET['page'])) {
}

So that if people just go to let’s say http://localhost/topic.php?id=1, it’ll display what was suppose to be displayed on the first page.

The second if statement follows the same logic and tests to see if someone has inputted http://localhost/topic.php?id=1&page=1 into their address bar.

if(isset($_GET['page'])) {
} elseif($_GET['page'] == 1) {
}

The pages are not what I’m concerned about. What I’m concerned about is I don’t get why I’m seeing the data being pulled onto the next page along with the previous data from other pages.

Example of what it’s doing.

Page 1 = 20 rows / All are numbered from 1 - 20
Page 2 = 20 rows / Starts with 19 - 39
Page 3 = 20 rows / Start from 37 - 57
Page 4 = 20 rows / Starts from 54 - 74
Page 5 = 20 rows / Starts from 70 - 90
Page 6 = 20 rows / Starts from 85 - 105 - This is what I’m having problems with

I don’t want the page to take 1 row from every page. Like each page, it takes that amount of rows from the previous page. I want the page to stay with the same rows. Like when I go to let’s say page 6, I’ll see page 5’s rows with the data IDs of 85, 86, 87, 88, 89, and 90. That’s not what I want.

This is what I really want.

Page 1 = 20 rows / All are numbered from 1 - 20
Page 2 = 20 rows / Starts with 21 - 41
Page 3 = 20 rows / Start from 42 - 62
Page 4 = 20 rows / Starts from 63 - 83
Page 5 = 20 rows / Starts from 84 - 104
Page 6 = 20 rows / Starts from 105 - 125

That’s what I want. Each page goes up by 1 because I don’t want to show what you already saw on page 5 if you’re on page 6. Do you get what I mean? What my script is doing is it’s showing previous comments when that’s not what I want. I want it to show new comments. The old comments from previous page is pushing the new comments to the next page and when you are on the next page, it pulls the old comments from the previous page. At the same time, for every page, that’s how many rows are being pushed forward.

So if you’re on page 4, about 3 rows of comments from page 3 appears on page 4 and when you’re on let’s say page 10, about 9 rows of comments from page 9 is being displayed on page 10 and that’s basically pushing what is really suppose to be on page 10 into page 11.

Another example.

Page URL is ?page=15, let’s say my intentions are to pull #IDs number 350 - 370 into the URL page ?page=15.
But this is what you’ll actually see.

#ID: 336 | #ID: 337 | #ID: 338 | #ID: 339 | #ID: 340 | #ID: 341 | #ID: 342 | #ID: 343 | #ID: 344 | #ID: 345 | #ID: 346 | #ID: 347 | #ID: 348 | #ID: 349 | #ID: 350 | #ID: 351 | #ID: 352 | #ID: 353 | #ID: 354 | #ID: 355

Do you see where it’s going wrong? For every ?page=, it takes off that much from the rows. I’m so stuck on this, but it doesn’t really matter anyways if no one can solve this. I’m kind of explaining it different and it’s difficult to explain it in a way that everyone can understand because you don’t know what I’m trying to achieve even if I explained it thoroughly and you don’t really know what’s wrong with my script. Sorry if I sound rude.