Pagination help

[yandoo]

Hiya I have pagination setup and on its own it works fine. The problem is that when I modify the query to Order by a $sort variable or search for a specific subcategory variable it only every works on the first page. When I click page 2 the Order by or Where variable is no longer applied. 

 

I added echos to each query to prove that when I hit page 2,3 etc. the initial query it runs on is not working and it always defaults back to both order by and sort variables being empty. 

if (isset($_POST['order'])) {
 $sort = $_POST['order'];

 }


  if (isset($_GET['subcategory'])) {
$subcategory = $_GET['subcategory'];
}

if (!empty($subcategory)){ $sql = mysql_query("SELECT * FROM products WHERE category='Blog' AND subcategory ='$subcategory' ORDER BY date_added DESC"); echo 'not empty sub'; }

else if (empty($subcategory) && !empty($sort)){ $sql = mysql_query("SELECT * FROM products WHERE category='Blog' ORDER BY '$sort' DESC"); echo 'empty sub, not empty sort'; }

else if (empty($subcategory) && empty($sort)){ $sql = mysql_query("SELECT * FROM products WHERE category='Blog' ORDER BY date_added DESC"); echo 'empty sub, empty sort'; } $productCount = mysql_num_rows($sql); // count the output amount

//////////////////////////////////// Pagination Logic ////////////////////////////////////////////////////////////////////////
$nr = $productCount; // Get total of Num rows from the database query
if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
    //$pn = ereg_replace("[^0-9]", "", $_GET['pn']); // filter everything but numbers for security(deprecated)
} 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 = 1; 
// 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
// This section is explained well in the video that accompanies this script
$centerPages = "";
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
} else if ($pn == $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a>  ';
} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
}
// 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; 



//////////////////////////////// END  Pagination Logic 

////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////  Pagination Display Setup /////////////////////////////////////////////////////////////////////
$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is ot 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 .=  '   <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 .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
    } 
}
///////////////////////////////////// END  Pagination Display Setup /
 

Any ideas how I might be able to get it work as intended please?

 

Thank you

Edited July 8, 2013 by yandoo

[yandoo]

The $sort variable is posted from a form within the page and the $subcategory is parsed as a url parameter from a different page. So it would occur to me that each time the page 2,3,etc is clicked the url parameter or form variable is no longer active, which is why it always reverts back to both of them being empty. So I guess my question is how do I gt it to include these variables?

 

Thanks

[QuickOldCar]

You have to also include the subcategories GET in the next and previous hyperlinks you generate

 

 

$paginationDisplay .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '&subcategory=' . $subcategory . '"> Next</a> ';

 

And you should be making any data inserted into mysql be safe...escaped, trim the GET so isn't whitespace

 

Gonna post you the php freaks pagination tutorial, and also 2 pagination scripts I made, maybe something of use will be in them for you or get a better idea.

 

http://www.phpfreaks.com/tutorial/basic-pagination

 

This one has a set 10 per page results and jumping x amount as needed

http://dynaindex.com/paginate

 

This one can set the amount to what want, but the navigation is more basic.

http://dyna.dyndns.tv/paginate/

[mac_gyver]

rather than adding your search/sort get parameters explicitly in each link, you should build a variable that contains the search/sort get parameters in one place and add that variable into each link (DRY - Don't Repeat Yourself), or even better yet, since the search/sort are already $_GET parameters (or they should be), just use http_build_query() to add your pn=x get parameter in the pagination logic into any existing get parameters without caring what any other code in your application might be setting as get parameters.

[yandoo]

Thank you both very much for the reply. I think I shoud go with the http_build_query() on the pagination links. I'm not sure how to do this, I've looked at the php manual.net but am still unsure as i've never done this before. Could you please help me by showing me an example in my problems context please?

 

Thank you