Pagination Error?..

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
truepal20032001
Forum Commoner
Posts: 27
Joined: Mon Jun 25, 2007 8:28 pm

Pagination Error?..

Post by truepal20032001 »

Ok i started to make a pagination code for my website, but what i expected did not occur, either way here is my database relating to the code..

Code: Select all

SQL result

Host: localhost
Database: news
Table : news

Rows: 6
=================================================================
id   |     title 	           |                  news
=================================================================
1          A                                           ABC
2          Ab                                         ABC
3          Ac                                          ABC
4          Ad                                         ABC
5          Ae                                         ABC
6          Af                                          ABC
As for this is the actual code.

Code: Select all

<?php
//Database connection:
$user = "root3";
$pass = "password3";
$host = "localhost";
$db	  = "news";
$con  = mysql_connect($host,$user,$pass);
		mysql_select_db($db,$con) or die(mysql_error());
		if ($con){
			echo "Successful database connection<br>";
		} else {
			echo "No successful connection<br>";
			}
$pageNumber = (isset($_GET["page"]) && is_numeric($_GET["page"])) ? $_GET["page"] : 1;

// Results per page
$perPage = 6;
// Establish a padding value
$padding = 3;
// Get start index of results
$startIndex = ($pageNumber * $perPage) - ($perpage - 1);
// Get total number of database entries
$totalCount = "SELECT COUNT(*) as 'Total' FROM news.news";
$rsCount = mysql_query($totalCount, $con) or die(mysql_error());
$rowCount = mysql_fetch_object($rsCount);
// Get page results
$sql = "SELECT id, title, news
          FROM news ORDER BY id 
			LIMIT $startIndex, $perPage";
// Get result set
$rs = mysql_query($sql,$con) or die(mysql_error());
// Do we have results?
if(mysql_num_rows($rs) > 0) {
// Show results:
	while($row = mysql_fetch_object($rs)) {
	print "<div>";
	print  $row->id . "<br>";
	print  "<strong>" . $row->title . "</strong><p>";
	print  ": ";
	print  $row->news . "</div><br>";
}
	} else {
		print "There are no records found!";
		}
// Close the database connection
mysql_close($con);


?>
AS FOR THE UNWANTED RESULT
=========================================
Successful database connection
There are no records found!
=========================================

Please tell me whats wrong :(
btw, its php5
thanks in advance
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Looking at your code, the query would appear to be..

Code: Select all

SELECT id, title, news FROM news ORDER BY id LIMIT -1, 6
Which is probably why it isn't returning any records.
truepal20032001
Forum Commoner
Posts: 27
Joined: Mon Jun 25, 2007 8:28 pm

Post by truepal20032001 »

astions wrote:Looking at your code, the query would appear to be..

Code: Select all

SELECT id, title, news FROM news ORDER BY id LIMIT -1, 6
Which is probably why it isn't returning any records.
No, i don't recall the "-1", the result of $startIndex should be 1 not -1, because the equation is:

Code: Select all

$startIndex = ($pageNumber * $perPage) - ($perpage - 1);
$startIndex = (1 * 6) - (6 - 1);
$startIndex = 1
so the query is:

Code: Select all

SELECT id, title, news FROM news ORDER BY id LIMIT 1, 6
Last edited by truepal20032001 on Mon Jun 25, 2007 9:17 pm, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

echo the query.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

The equation you want is (($page - 1) * $limitPerPage). Page one starts at $limit * 0 (0), page two starts at the l$imit * 1, page three starts at the $limit * 3, etc.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

truepal20032001 wrote:so the query is:

Code: Select all

SELECT id, title, news FROM news ORDER BY id LIMIT 1, 6
Limit should be 0,6, but even at 1, 6 it should still pull 5 records. Something isn't right.
truepal20032001
Forum Commoner
Posts: 27
Joined: Mon Jun 25, 2007 8:28 pm

Post by truepal20032001 »

deamn! thats right!
sorry for doubting you astions, and thankyou surperdesignz for elaborating it
you got my blessing!
truepal20032001
Forum Commoner
Posts: 27
Joined: Mon Jun 25, 2007 8:28 pm

Post by truepal20032001 »

yep, after inserting (($page - 1) * $limitPerPage), everything came out like butter!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Code: Select all

$startIndex = ($pageNumber * $perPage) - ($perpage - 1);
In that line, the last $perpage was undefined, because you used a capital P elsewhere in the script as well.
truepal20032001
Forum Commoner
Posts: 27
Joined: Mon Jun 25, 2007 8:28 pm

Post by truepal20032001 »

astions wrote:

Code: Select all

$startIndex = ($pageNumber * $perPage) - ($perpage - 1);
In that line, the last $perpage was undefined, because you used a capital P elsewhere in the script as well.
deamn smart you're right!, silly me., well i really appriciate this astions, thanks man
Post Reply