   [web] Difficulty with PHP/MySql Sorting Script
  Hi thereI have written a sorting engine which displays non expired records, but im having difficulties getting it to work. It was working fine until I added pagination, and now it seems to have gone to pot.The script works by getting todays date, and based on the users selection from the drop down menu, calculates the number of pages, and records the users sort preference.The correct number of links to different pages are then generated, and the pages are populated based on the users sort preference, or so I thought...The first page of results is sorted correctly, but on the second and subsequent pages, this isnt always the case. This causes results to be appearing in the wrong order, or not at all in some cases.Ive included my source code, if anyone see's anything which is glaringly obvious, or feels like giving me a hand fixing this id very much appreciate it :)<?php//gets todays date$date = gmdate(Ymd);//gets the connection settingsrequire('connection.php');//gets the variables the user selected from the form.$select= $_POST['select'];$searchTerm=$_POST['searchTerm'];$searchType=$_POST['searchType'];//Switch Statement which correspsonds to the sort drop down menu.  Each case represents a field in the menu.  The query contained gets all valid records (i.e. non expired)//and $orderBy represents the order that the results with be sorted into.switch($select){case "1":$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";$orderBy = "area";break;case "2":$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";$orderBy = "expireDate";break;case "3":$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";$orderBy = "title";break;case "4":$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";$orderBy = "type";break;case "5":$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";$orderBy = "Value";break;}//If the user hasnt selected a sorting preference, just display all the records which havent expiredif($select==''){$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";}$result = $db->query($query);  $total_entries = $result->num_rows;//Number of entries that each page will contain$entries_per_page = 4; //Gets the current page numberif(isset($_GET['page_number'])) { $page_number = $_GET['page_number']; } else{ $page_number = 1; }//Calculates the number of pages needed to display all the records$total_pages = ceil($total_entries / $entries_per_page); //calculates the offset i.e. calculates whats the first record to be displayed on a certain page$offset = ($page_number - 1) * $entries_per_page; //If no search terms or sort order has been entered, then display all the current valid records across the pagesif($select=='' && $searchTerm==''){$queryA = "select * from adverts where expireDate >= ".$date." limit $offset,$entries_per_page";$resultA = $db->query($queryA);$total_entries = $result->num_rows;}//If the user enteres a search term, display it across the pagesif($searchTerm!=''){$queryA = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts where match(".$searchType.") against ('".$searchTerm."%' in boolean mode) ";$resultA = $db->query($queryA);$total_entries = $result->num_rows;}//If the user enters a sort perference, display the records in that order across the pagesif($select!=''){$queryA = "select * from adverts where expireDate >= ".$date." order by ".$orderBy." asc limit $offset,$entries_per_page";$resultA = $db->query($queryA);$total_entries = $result->num_rows;}//Display links to all the pageecho'<p>Page: ';for($i = 1; $i <= $total_pages; $i++) { if($i == $page_number) { // This is the current page. Don't make it a link. print "$i "; } else { // This is not the current page. Make it a link. echo'<a href="viewer.php?page_number='.$i.'">&nbsp;'.$i.'&nbsp;</a>'; } } echo'</p>';//Calculates if there will be any pages that dont have the maximum number of recrods allowed$remaining = $total_entries % $entries_per_page;//if thats the case, display the records across all the pages if($remaining==0) { if($page_number <= $total_pages) {//this script displays the adverts based on the search resultsfor ($i=1; $i <=$entries_per_page; $i++){$row = $resultA->fetch_assoc();require('displaySearchResultsScript.php');}}}//else display all the recrods bar the last page, we will handle this on its ownelse{//Displays all records bar the first page if($page_number < $total_pages) {//this script displays the adverts based on the search resultsfor ($i=1; $i <=$entries_per_page; $i++){$row = $resultA->fetch_assoc();require('displaySearchResultsScript.php');}}//Displays the very last page on its own as it has a unique number of recordsif($page_number == $total_pages) {//this script displays the adverts based on the search resultsfor ($i=1; $i <=$remaining; $i++){$row = $resultA->fetch_assoc();require('displaySearchResultsScript.php');}}}?>  [Edited by - Sonnenblume on October 18, 2006 9:45:58 AM]
  For the love god, use the SOURCE tags...Also, how about using the LIMIT param in the query? Add a $PageSize = 20; constant somewhere, and then use it to calc the LIMIT part:"QUERY HERE LIMIT $Page * $PageSize, $Page + 1 * $PageSize";Toolmaker   ;  Ok, ive not got it working but I think ive figured out whats wrong with it.First of all, say im showing 5 records per page.  Its sorting my results into alphabetical order, and then on printing out records 1-5 in the correct order on the first page.  However, when the user clicks on the second page, it is forgetting that the results have all been sorted, and then taking records 5-10 out of the database, rather than the sorted results Ive produced.Any suggestions on how to work around this?   ;  I know whats wrong.  When I click on a link to view the next set of results, its forgetting what the 'orderBy' value is.  Ill go away and find a way around that.   ;  As toolmaker said, use the LIMIT statement and let MySQL do the sorting. It's a thousand times better at it than you are. It was built to do that.Small fix to toolmaker's snippet:"QUERY HERE LIMIT $Page - 1 * $PageSize, $Page * $PageSize";This way you'll actually get 1-$PageSize on the first page, instead of the zeroth page.   ;  One more small fix, "QUERY HERE LIMIT ($Page - 1) * $PageSize, $Page * $PageSize";Can't forget the parenthesis or else operator precedence takes over and evaluates 1 * $PageSize first.   