andrew_ww Posted January 12, 2008 Share Posted 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); ?> Quote Link to comment https://forums.phpfreaks.com/topic/85699-sorting-problem/ Share on other sites More sharing options...
Ken2k7 Posted January 12, 2008 Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/85699-sorting-problem/#findComment-437399 Share on other sites More sharing options...
andrew_ww Posted January 12, 2008 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/85699-sorting-problem/#findComment-437407 Share on other sites More sharing options...
Ken2k7 Posted January 12, 2008 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/85699-sorting-problem/#findComment-437410 Share on other sites More sharing options...
andrew_ww Posted January 13, 2008 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/85699-sorting-problem/#findComment-437859 Share on other sites More sharing options...
twostars Posted January 13, 2008 Share Posted 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 . Quote Link to comment https://forums.phpfreaks.com/topic/85699-sorting-problem/#findComment-437860 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.