Forum Moderators: coopster
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 :)
<!-- This is the HTML Form where the user selects if they wish to sort or search for records -->
<form name="form1" method="post" action="viewer.php">
<table width="188" border="0" cellpadding="0" cellspacing="0">
<tr>
<td><select name="select">
<option value="0" selected>Sort All Adverts</option>
<option value="1">Sort by Area</option>
<option value="2">Sort by Date</option>
<option value="3">Sort by Title</option>
<option value="4">Sort by Type</option>
<option value="4">Sort by Value</option>
</select></td>
<td width><input type="image" src="graphics/query.jpg" alt="submit" title="submit" value="Submit"></td>
</tr>
</table>
</form>
</td>
<td align="center" ><form name="form2" method="post" action="viewer.php">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td><input name="searchTerm" type="text" value="Search for" size="10"> </td>
<td><select name="searchType">
<option value="title">Title</option>
<option value="cpv">CPV Code</option>
<option value="type">Contract Type</option>
<option value="expireDate">Deadline YYYYMMDD</option>
<option value="projectNum">Number</option>
<option value="details">Details</option>
</select></td>
<td><input type="image" src="graphics/query.jpg" alt="submit" title="submit" value="Submit"></td>
</tr>
</table>
</form></td>
</tr>
</table>
<?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 = 6;
//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." order by expireDate desc 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.'"> '.$i.' </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 at 2:06 pm (utc) on Oct. 18, 2006]
Well I`m guessing that when you submit the form via POST, the order by is populated based on the selection. However, once you switch to your pages links, you are hitting a GET request and the order by option then has no value?
To make things easier I would use a GET query for the search and then just pass the values to the next page in the query string.
Bottom line is you need to populate your POST variables into your GET query.
Did that make any sense?
dc
