Stumped on sorting issues

[Kaylech]

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:

$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;

 

[ChrisHirst]

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.

Probably your "clickable header" are not passing the 'pagination' information to set the limits in the query.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[Kaylech]

Thanks Chris for the prompt response.

I thought about that too but here is just one clickable column header:

   <th style="width:100px;">Details</th>
   <th style="color:white;background-color: #8C8C8C;text-decoration: none;font-weight:bold;width:100px;"><a style="text-decoration: none; color: white;font-size:10pt;" href='?sort=BidDate&order=<?php echo $order == 'ASC' ? 'DESC' : 'ASC' ?>'>Issue Date<?php
	if($order=="ASC" && $sort=="BidDate"){
	echo '<img src="images/arrow-up.png" width="10px" height="10px">';
	}
	if($order=="DESC" && $sort=="BidDate"){
	echo '<img src="images/arrow-down.png" width="10px" height="10px">';
	}
	?></a></th>

What am I missing here?

 

[spamjim]

You're missing the GET variable that includes the search term. You need to pass that search term again.

 

[Kaylech]

Hi spamjim,

How would you go about pasing $_GET[$fieldPost]

In the first code I posted, this -> $searchType; would translate to department=whatever value, assuming department is selected.

So, I attempted passing the $searchType to the sort/order link which is not working of course.

Now, you are suggesting that I pass $_GET[fieldPost]?

How would you go about doing that?

Here again is one of the sort/order links:

  <th style="color:white;background-color: #8C8C8C;text-decoration: none;font-weight:bold;width:100px;"><a style="text-decoration: none; color: white;font-size:10pt;" href='?$_GET[$fieldPost]&sort=BidDate&order=<?php echo $order == 'ASC' ? 'DESC' : 'ASC' ?>'>Issue Date<?php
	if($order=="ASC" && $sort=="BidDate"){
	echo '<img src="images/arrow-up.png" width="10px" height="10px">';
	}
	if($order=="DESC" && $sort=="BidDate"){
	echo '<img src="images/arrow-down.png" width="10px" height="10px">';
	}
	?></a></th>

Notice how I attempted to pass $_GET[$fieldPost].

Thanks alot

 

[Kaylech]

Ok, got it working now.

I was using it incorrectly.

This is the code that worked:

   <th style="color:white;background-color: #8C8C8C;text-decoration: none;font-weight:bold;width:100px;"><a style="text-decoration: none; color: white;font-size:10pt;" href='?sort=BidDate&order=<?php echo $order == 'ASC' ? 'DESC' : 'ASC' [b]?>&<?php echo $searchType ?>[/b]'>Issue Date<?php
	if($order=="ASC" && $sort=="BidDate"){
	echo '<img src="images/arrow-up.png" width="10px" height="10px">';
	}
	if($order=="DESC" && $sort=="BidDate"){
	echo '<img src="images/arrow-down.png" width="10px" height="10px">';
	}
	?></a></th>

Notice the searchType.