Sort by column name desc asc in pagination.

[chris11]

Hello and Gm.

I have a script (below) which paginates a mysql table. Showing 4 rows per page. It works well.

 

   <?php

$sql = mysql_query("SELECT * FROM tablename ORDER BY id ASC");

$nr = mysql_num_rows($sql); 
if (isset($_GET['pn'])) { 
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); 
   
} else {
    $pn = 1;
} 

$itemsPerPage = 8; //number of rows per page 

$lastPage = ceil($nr / $itemsPerPage);

if ($pn < 1) {
    $pn = 1; 
} else if ($pn > $lastPage) { 
    $pn = $lastPage;
} 

$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>  ';
}

$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 

$sql2 = mysql_query("SELECT * FROM tablename ORDER BY id ASC $limit"); //second query

$paginationDisplay = ""; 
if ($lastPage != "1"){
   
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '        ';

    if ($pn != 1) {
        $previous = $pn - 1;
        $paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
    } 
  
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';

    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
        $paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
    } 
}

$outputList = '';
while($row = mysql_fetch_array($sql2)){  


print "$row[permalink]";
print "$row[title]";

  } 
    
  ?>
</div>

  

<h2>Total Items: <?php echo $nr; ?></h2>

      <?php echo $paginationDisplay; ?>
      <?php print "$outputList"; ?>

        

 

What I would like to do is have it like you see on other sites where there is a drop down menu that one can sort by a column first and it paginates after.

 

So the url or dropdown would look something like

 

/paginatedfile.php/column_name=title&orderby=desc

 

And if you click page 2 you get a url like..

 

/paginatedfile.php/column_name=title&orderby=desc&pn=2

 

Can someone please help.

 

 

Thanks, Chris

 

 

 

 

[Rifts]

using $sql = mysql_query("SELECT * FROM tablename ORDER BY id ASC");

 

 

make a link with value ACS and one with DESC send this via GET

 

on new page use $_GET['order']

 

then in query ull have

 

$sql = mysql_query("SELECT * FROM tablename ORDER BY id $_GET['order'] ");

 

make sure to sanitize value first

[jcbones]

Something like this?

<?php
$order = (isset($_GET['o']) && in_array(strtoupper($_GET['o']), array('ASC','DESC'))) ? strtoupper($_GET['o']) : 'ASC';
$sql = mysql_query("SELECT * FROM tablename ORDER BY id $order");

$nr = mysql_num_rows($sql); 
if (isset($_GET['pn'])) { 
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); 
   
} else {
    $pn = 1;
} 

$itemsPerPage = 8; //number of rows per page 

$lastPage = ceil($nr / $itemsPerPage);

if ($pn < 1) {
    $pn = 1; 
} else if ($pn > $lastPage) { 
    $pn = $lastPage;
} 

$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 . '&o=' . $order . '">' . $add1 . '</a>  ';
} else if ($pn == $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '&o=' . $order . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '&o=' . $order . '">' . $sub2 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '&o=' . $order . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '&o=' . $order . '">' . $add1 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '&o=' . $order . '">' . $add2 . '</a>  ';
} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '&o=' . $order . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '&o=' . $order . '">' . $add1 . '</a>  ';
}

$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 

$sql2 = mysql_query("SELECT * FROM tablename ORDER BY id $order $limit"); //second query

$paginationDisplay = ""; 
if ($lastPage != "1"){
   
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '        ';

    if ($pn != 1) {
        $previous = $pn - 1;
        $paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '&o=' . $order . '"> Back</a> ';
    } 
  
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';

    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
        $paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '&o=' . $order . '"> Next</a> ';
    } 
}

$outputList = '';
while($row = mysql_fetch_array($sql2)){  


print "$row[permalink]";
print "$row[title]";






  } 
    
  ?>
</div>

  

<h2>Total Items: <?php echo $nr . '( <a href="?pn=' . $pn . '&o=ASC">Ascending</a>/<a href="?pn=' . $pn .'&o=DESC">Descending</a> )'; ?></h2>

      <?php echo $paginationDisplay; ?>
      <?php print "$outputList"; ?>

        

[Rifts]

that looks good does it work?

[chris11]

It does work!! Works well.

 

How do I add column in there. so I can have an option to sort a particular column. A link that says "order by permalink desc"

"order by permalink asc"

 

Something like this?

 

$column_name=$_GET['column_name'];

 

 

 

 

[chris11]

K, I think I got it. I added this to

 

$column_name=$_GET["c"];
if(isset($_GET["c"]) and strlen($column_name)>0){
$column_name=$_GET["c"]; 
}
else{
$column_name="title";
} 

 

Then in the second query for the column name "id" I changed it to $column_name