Page 1 of 1

Pagination Error?..

Posted: Mon Jun 25, 2007 8:46 pm
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

Posted: Mon Jun 25, 2007 8:52 pm
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.

Posted: Mon Jun 25, 2007 9:10 pm
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

Posted: Mon Jun 25, 2007 9:14 pm
by Benjamin
echo the query.

Posted: Mon Jun 25, 2007 9:16 pm
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.

Posted: Mon Jun 25, 2007 9:21 pm
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.

Posted: Mon Jun 25, 2007 9:21 pm
by truepal20032001
deamn! thats right!
sorry for doubting you astions, and thankyou surperdesignz for elaborating it
you got my blessing!

Posted: Mon Jun 25, 2007 9:24 pm
by truepal20032001
yep, after inserting (($page - 1) * $limitPerPage), everything came out like butter!

Posted: Mon Jun 25, 2007 9:27 pm
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.

Posted: Mon Jun 25, 2007 9:29 pm
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