php paging error

[dhani09]

I'm using the following code for my paging on my script

<?php
$pn = 0;


$result = mysql_query("SELECT * FROM tbl_product WHERE category = '$cat' ORDER BY id ASC ");

$nr = mysql_num_rows($result);
if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
    $pn = $_GET['pn'];// filter everything but numbers for security(new)
} else { // If the pn URL variable is not present force it to be value of page number 1
    $pn = 1;
} 
//This is where we set how many database items to show on each page 
$itemsPerPage = 5; 
// Get the value of the last page in the pagination result set
$lastPage = ceil($nr / $itemsPerPage);
// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
if ($pn < 1) { // If it is less than 1
    $pn = 1; // force if to be 1
} else if ($pn > $lastPage) { // if it is greater than $lastpage
    $pn = $lastPage; // force it to be $lastpage's value
} 

// This creates the numbers to click in between the next and back buttons
$centerPages = ""; // Initialize this variable
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
} else if ($pn == $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a> &nbsp;';
} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
}
// This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
$limit = 'LIMIT '.($pn - 1) * $itemsPerPage .','.$itemsPerPage; //returns negative range
// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
// $sql2 is what we will use to fuel our while loop statement below


$result2 = mysql_query("SELECT * FROM tbl_product WHERE category = '$cat' ORDER BY id ASC $limit");//query fails
if(!$result2)
echo  "ERROR".mysql_error();

//PAGINATION DISPLAY

$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is not equal to 1, if it is only 1 page we require no paginated links to display
if ($lastPage != "1"){
    // This shows the user what page they are on, and the total number of pages
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '';
    // If we are not on page 1 we can place the Back button
    if ($pn != 1) {
        $previous = $pn - 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn='.$previous.'"> Back</a> ';
    } 
    // Lay in the clickable numbers display here between the Back and Next links
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
    // If we are not on the very last page we can place the Next button
    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn='.$nextPage.'"> Next</a> ';
    } 
}

?>
<!-- SOME HTML STUFF -->

THEN I USE THIS LOOP TO RETRIEVE ITEMS IN THE HTML BODY

<?php
while($row = mysql_fetch_row($result2)){
        echo '<div class="product_info">';
          echo '<div class="category_product_number">#'.$row[0].'('.$row[1].')'.'</div>';
         echo '<div class="category_product_description">'.$row[2].'</div>';
         echo '<div class="category_product_price">'.$row[4].'TL</div>';
      echo '</div>';
      }

      ?>

AND TO DISPLAY THE PAGINATION I DO THIS SOMEWHERE BELOW THIS LOOP
<div class="central_container"><span class="current_page">Showing <?php echo $paginationDisplay; ?></span></div>

HOWEVER, when i run the code, i get this error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-5,5' at line 1".

It seems i get a negative page number when i click the "NEXT" link or try to move to a new page (by clicking that page number), so the query returns a negative range for LIMIT. But I don't know why because i have a conditon that makes sure there are no negative page numbers. I know it's a lot of code but any help figuring out the issue will be appreciated. Thanks

[minitauros]

Well this is some weird shit indeed, there does not seem to be anything wrong with your code. Have you tried echo-ing $limit? E.g.:

echo $limit;

And see what it is? Is it really -5,5? If yes, you could try echo-ing $pn each time its value changes, to see where it goes wrong.