×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Stumped on sorting issues

Stumped on sorting issues

Stumped on sorting issues

(OP)
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:

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; 



RE: Stumped on sorting issues

Quote:

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

RE: Stumped on sorting issues

(OP)
Thanks Chris for the prompt response.

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

CODE

<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?

RE: Stumped on sorting issues

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

RE: Stumped on sorting issues

(OP)
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:

CODE

<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

RE: Stumped on sorting issues

(OP)
Ok, got it working now.

I was using it incorrectly.

This is the code that worked:

CODE

<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' ?>&<?php echo $searchType ?>'>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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close