# [web] Difficulty with PHP/MySql Sorting Script

This topic is 4236 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

Hi there I 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 settings require('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 expired
if($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 number if(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 pages if($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 pages
if($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 pages if($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 page
echo'<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 results for ($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 own else { //Displays all records bar the first page if($page_number < $total_pages) { //this script displays the adverts based on the search results for ($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 records if($page_number == $total_pages) { //this script displays the adverts based on the search results for ($i=1; $i <=$remaining; $i++) {$row = $resultA->fetch_assoc(); require('displaySearchResultsScript.php'); } } } ?>  [Edited by - Sonnenblume on October 18, 2006 9:45:58 AM] #### Share this post ##### Link to post ##### Share on other sites Advertisement 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

##### Share on other sites
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?

##### Share on other sites
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.

##### Share on other sites
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";

• 10
• 40
• 15
• 10
• 23