-1

I was looking for a simple pagination script and found one here, this seems to be working just fine.

However, when i click on "2", as in page 2, it just shows the records of page 2 underneath those that are already there. So basically if I would click on page 214 it still shows all of the records on one page.

I am not very experienced with PHP so i couldn't figure out what was wrong with the paginator.class.php, hopefully someone here can.

This is the code on the page where it should do the pagination:

else {
$query = "SELECT COUNT(*) FROM products";
$result = mysql_query($query) or die(mysql_error());
$num_rows = mysql_fetch_row($result);
$pages = new Paginator;  
$pages->items_total = $num_rows[0];  
$pages->mid_range = 9;  
$pages->paginate();  

$query1 = "SELECT serial, name, description, price, picture FROM products WHERE serial != '' ORDER BY serial ASC $pages->limit";
$result = mysql_query($query1) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
echo '<div style="margin-bottom:10px;display:inline-block;background-color:#E3E3E3;width:190px;height:200px;"><a href="'.$_SERVER['PHP_SELF'].'?serial='.$row['serial'].'"><img style="padding-top:10px;padding-left:25px;width:150px;height:150px;" src="'.htmlspecialchars($row['picture']).'"></a><br><div align="center"><b>'.htmlspecialchars($row['name']).'</b><br><h6>&euro;'.htmlspecialchars($row['price']).'</h6></div></div>&nbsp;';
};
echo '&nbsp;';
echo '<br><br><div style="margin-left:330px;">';
echo $pages->display_pages();
echo '</div>';
}

The paginator.class.php can be found on the website I just mentioned.

Thakkennes
  • 215
  • 2
  • 3
  • 12

1 Answers1

0

The issue lies in your query:

"SELECT serial, name, description, price, picture FROM products WHERE serial != '' ORDER BY serial ASC $pages->limit"

You need to determine what the value of $pages->limit is. It seems to me that instead of calculating how many records should be displayed on each page (let's say 10 for argument's sake) and then determining what page you're on and setting the LIMIT condition.

What it should be set to is something like this:

LIMIT 30, 10

That's for page 4 - it displays records 30-40, rather than what I suspect it's doing, which is 

LIMIT 40

That line will simply show up to 40 records and not close the lower bound of the window.

FYI take a look at the MySQL SELECT syntax in the manual.

Matt
  • 6,993
  • 4
  • 29
  • 50
  • as far as I know it does make that statement in lines 99-101: $this->low = ($this->current_page-1) * $this->items_per_page; $this->high = (isset($_GET['ipp']) == 'All') ? $this->items_total:($this->current_page * $this->items_per_page)-1; $this->limit = (isset($_GET['ipp']) == 'All') ? "":" LIMIT $this->low,$this->items_per_page"; or is this not the right way to set this statement? – Thakkennes Aug 02 '12 at 13:09
  • What are the values of a) `$pages->limit` and b) `$query`? – Matt Aug 02 '12 at 13:11
  • I think it's this: $this->limit = (isset($_GET['ipp']) == 'All') ? "":" LIMIT $this->low,$this->items_per_page"; $query is the line you mentioned in your answer.. – Thakkennes Aug 02 '12 at 13:12
  • SMH...`echo` the values to the screen, then `exit()`. You'll see their values *clear as day*, no guess-work. – Matt Aug 02 '12 at 13:14
  • I get: LIMIT 0,8 and SELECT COUNT(*) FROM products but the outcome of that should be 10 as that is the total amount of records. – Thakkennes Aug 02 '12 at 13:19
  • OK, so that must be for page 1. What about page 2? – Matt Aug 02 '12 at 13:20
  • What does your *second* query string evaluate to? Paste this to the line above your `while` loop: `echo "SELECT serial, name, description, price, picture FROM products WHERE serial != '' ORDER BY serial ASC $pages->limit";` – Matt Aug 02 '12 at 13:24
  • that gives me this: SELECT serial, name, description, price, picture FROM products WHERE serial != '' ORDER BY serial ASC LIMIT 0,8 – Thakkennes Aug 02 '12 at 13:25
  • If you're trying to limit the number of records per page, then the limit is coming back correctly. On page 2, it should result in `LIMIT 8, 8` since your offset should be 8 rows, and you want 8 more rows displayed. Try it on page 2. – Matt Aug 02 '12 at 13:26
  • it just gives me this: SELECT serial, name, description, price, picture FROM products WHERE serial != '' ORDER BY serial ASC , There is no limit statement.. – Thakkennes Aug 02 '12 at 13:29
  • In that case there's an issue with your pagination script. If all it's doing is figuring out what the window should be, that's fairly easy. It just takes a little thought and effort. One MAJOR issue I noticed with your code is that you're still using `mysql_*` functions. These are being deprecated and it's advised that they not be used anymore. You should switch to [PDO](http://php.net/manual/en/book.pdo.php) or [mysqli](http://php.net/manual/en/book.mysqli.php). http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons – Matt Aug 02 '12 at 13:33
  • right, I'll look into that as well then, I'll check the pagination.class.php and hopefully that will help, thanks for your help! – Thakkennes Aug 02 '12 at 13:36
  • One final thought on that pagination algo...all it needs to do (in order to get you to the correct page) is a) ensure that it's a valid page (page * rows_per_page <= total_rows && page != 0), b) get you to the right page (`return "LIMIT " . ($rows_per_page * $page) . ", " . $rows_per_page`). That's it! – Matt Aug 02 '12 at 13:39
  • Yes but that's _exactly_ what it does in lines 99-101, like I showed you earlier. That's why I don get what I am doing wrong.. – Thakkennes Aug 02 '12 at 13:48