Our app is set up in such that a user enters a search value into a textbox or selects a value from the dropdown and hits the search button.
If records are found, they are displayed.
This part works great.
Then there is the paging and sorting component.
Each page holds 20 records.
If there are more then 20 records, additional records are displayed in next pages.
For instance, if there are 62 records, then you have 4 pages. This works great as well.
The issue is that records are sorted in ASC(ending) and DESC(ending) order.
By default, the sort order is DESC. If you click any of the sortable headers to sort, then instead of 62 records based on your search results, entire records on the database are displayed.
Any ideas why this is happening or how to resolve it?
Here are two images to illustrate the problems I just described. The first image shows results of a search.
The second image shows how the results exploded to several records after you click a column header to sort.
Here are some code:
If records are found, they are displayed.
This part works great.
Then there is the paging and sorting component.
Each page holds 20 records.
If there are more then 20 records, additional records are displayed in next pages.
For instance, if there are 62 records, then you have 4 pages. This works great as well.
The issue is that records are sorted in ASC(ending) and DESC(ending) order.
By default, the sort order is DESC. If you click any of the sortable headers to sort, then instead of 62 records based on your search results, entire records on the database are displayed.
Any ideas why this is happening or how to resolve it?
Here are two images to illustrate the problems I just described. The first image shows results of a search.
The second image shows how the results exploded to several records after you click a column header to sort.
Here are some code:
Code:
$fields = array(
'projectTitle' => array('field' => 'b.BidTitle', 'searchType' => 'like'),
'BidType' => array('field' => 'b.BidType', 'searchType' => 'equal'),
'BidStatus' => array('field' => 'b.BidStatus', 'searchType' => 'equal'),
'department' => array('field' => 'b.AliasID', 'searchType' => 'equal'),
'bidId' => array('field' => 'b.BidID', 'searchType' => 'like'),
'txtFromDate' => array('field' => 'b.BidDate', 'searchType' => 'gte'),
'txtToDate' => array('field' => 'b.BidDate', 'searchType' => 'lte'),
'txtFromDueDate' => array('field' => 'b.DueDate', 'searchType' => 'gte'),
'txtToDueDate' => array('field' => 'b.DueDate', 'searchType' => 'lte'),
'bidDate' => array('field' => 'b.BidDate', 'searchType' => 'equal'),
'dueDate' => array('field' => 'b.DueDate', 'searchType' => 'equal')
);
$where = array();
$searchType = "";
foreach($fields as $fieldPost => $field) {
if(isset($_GET[$fieldPost]) && strlen($_GET[$fieldPost]) > 0) {
if($field['searchType'] == 'like') {
$where[] = "".$field['field']." LIKE '%" . ms_escape_string($_GET[$fieldPost]) . "%'";
} elseif ($field['searchType'] == 'gte') {
$where[] = "".$field['field']." >= '" . ms_escape_string($_GET[$fieldPost]) . "'";
}
elseif ($field['searchType'] == 'lte') {
$where[] = "".$field['field']." <= '" . ms_escape_string($_GET[$fieldPost]) . "'";
}
else {
$where[] = "".$field['field']." = '" . ms_escape_string($_GET[$fieldPost]) . "'";
}
$searchType .= (empty($searchType) ? "" : "&") . $fieldPost . "=" . $_GET[$fieldPost];
// echo $searchType;
}
}
//the query
$sql = " SELECT c.* FROM (
SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY $sort $order) AS RowID,b.ID,CONVERT(VARCHAR(11), b.BidDate, 106) sBidDate,CONVERT(VARCHAR(11), b.DueDate, 106) sDueDate,b.BidTitle,b.DueTime,b.BidID,b.BidIDFile,
da.DeptAlias,b.BidType,CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe convert(char(10),b.AwardDate,101)END AS sAwardDate,
CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS sLastUpdate,s.Status
FROM bids b inner join DeptALIAS da on b.AliasID = da.AliasID inner join Dept d on da.DeptCode = d.DeptCode inner join status s on b.BidStatus=s.StatusId " . ( count($where) > 0 ? " WHERE " . implode(' AND ', $where) : " " ) . " ORDER BY $sort $order
) AS c
WHERE c.RowID > $row_start AND c.RowID <= $row_end
";
//echo $sql;