Sorting Problem

andrew_ww · January 12, 2008

Hello,

I'm trying to building a page that has sorting / filtering and paging capabilities. So far I'm partial success on sorting and paging.

The attached code does both however whn I sort I loose the paging for some reason?

Does anything look out of place with my code??

Many Thanks.

<?php require_once('Connections/fitted.php');


// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;


$field = $_GET['sortby']; 


mysql_select_db($database_fitted, $fitted);
$query_rs_paging = "SELECT * FROM sheet1 LIMIT $offset, $rowsPerPage";

if(isset($field)){
    $query_rs_paging = $query_rs_paging.' ORDER BY '.$field.' ASC LIMIT $offset, $rowsPerPage'; //sort based on passed GET field
}     



$rs_paging = mysql_query($query_rs_paging, $fitted) or die(mysql_error());
$row_rs_paging = mysql_fetch_assoc($rs_paging);
$totalRows_rs_paging = mysql_num_rows($rs_paging);

// how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM sheet1";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

$first = " <a href=\"$self?page=1\">[First Page]</a> ";
} 
else
{
$prev  = ' '; // we're on page one, don't print previous link
$first = ' '; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
} 
else
{
$next = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}

?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<table width="50%" border="0" align="center">
  <tr>
    <td><div align="center"><strong><a href="paging.php?sortby=id">ID</a></strong></div></td>
    <td><div align="center"><strong><a href="paging.php?sortby=Column1">Column1</a> </strong></div></td>
    <td><div align="center"><strong><a href="paging.php?sortby=Column2">Column2</a></strong></div></td>
    <td><div align="center"><strong><a href="paging.php?sortby=Column2">Column3</a></strong></div></td>
  </tr>
  <?php do { ?>
    <tr>
      <td width="20%"><div align="center"><?php echo $row_rs_paging['id']; ?></div></td>
      <td width="40%"><div align="center"><?php echo $row_rs_paging['column1']; ?></div></td>
      <td width="40%"><div align="center"><?php echo $row_rs_paging['column2']; ?></div></td>
      <td width="40%"><div align="center"><?php echo $row_rs_paging['column3']; ?></div></td>
    </tr>
    <?php } while ($row_rs_paging = mysql_fetch_assoc($rs_paging)); ?>
</table>
<p> </p>
<table width="50%" border="1" align="center">
  <tr>
    <td><div align="center"><?php echo $first . $prev .
" Showing page $pageNum of $maxPage pages " . $next . $last; ?>
    </div>
    <div align="center"></div></td>
  </tr>
</table>
<br />
</body>
</html>
<?php
mysql_free_result($rs_paging);
?>

Ken2k7 · January 12, 2008

$query_rs_paging = "SELECT * FROM sheet1 LIMIT $offset, $rowsPerPage";

if(isset($field)){
    $query_rs_paging = $query_rs_paging.' ORDER BY '.$field.' ASC LIMIT $offset, $rowsPerPage'; //sort based on passed GET field
}

You're using limit twice. Why?

andrew_ww · January 12, 2008

I thought that would be necessary because the query is being called twice. Firstly when the page runs, and secondly when any of the sort links are clicked.

Ken2k7 · January 12, 2008

Well this line doesn't run a query:

$query_rs_paging = "SELECT * FROM sheet1 LIMIT $offset, $rowsPerPage";

That just stores a string. Nothing special there.

andrew_ww · January 13, 2008

I think the problem lies with this line. As when I build it manually (not relying on variables) it does work:

$query_rs_paging = $query_rs_paging.' ORDER BY '.$field.' ASC LIMIT $offset, $rowsPerPage'; //sort based on passed GET field

twostars · January 13, 2008

I think the problem lies with this line. As when I build it manually (not relying on variables) it does work:
$query_rs_paging = $query_rs_paging.' ORDER BY '.$field.' ASC LIMIT $offset, $rowsPerPage'; //sort based on passed GET field

Remove from the above code "LIMIT $offset, $rowsPerPage"

Also, you can use $query_rs_paging .= "ORDER BY.......

instead of $query_rs_paging = $query_rs_paging .

Sign In

Sorting Problem

Recommended Posts

andrew_ww

Link to comment

Share on other sites

Ken2k7

Link to comment

Share on other sites

andrew_ww

Link to comment

Share on other sites

Ken2k7

Link to comment

Share on other sites

andrew_ww

Link to comment

Share on other sites

twostars

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information