Page 1 of 1

Ranking question

Posted: Tue Dec 19, 2006 8:46 am
by timclaason
I'm revamping a Quality Assurance Queue, and one of the things components that needs to get added is a sorting feature.

For instance, there is a field in the table called 'rank.'

So, on HTML output, the queue may look something like this:

Rank TestName
------ -------------
1 Test A
2 Test B
... ...
25 Test Y
26 Test Z

When a user clicks on a particular test, it will give them a dropdown with values between 1 and #_of_Tests. The current rank will be the option that is selected.

I'm looking for some advice on how to do it.

I'm kind of thinking that I should do something like this:

Code: Select all

$rank = $_POST['rank'];
$testname = $_POST['testname'];
if($class->ValueIsChanged($testname)) { //Check to see if _POST value is different than DB value
   //If the value of the $_POST[rank] is higher than DB
   if($class->ValueIsLarger($testname, $rank) { 
        $class->UpdateRank($testname, $rank); //Update Selected testname to selected rank
        for($tablerank = $rank; $tablerank <= $class->numberOfActiveTest(); $tablerank++) {
             //Update all tests - set rank to be 1 
             //lower on all tests that are now a lower value rank than selected one
             $query = "UPDATE tests SET rank=$tablerank-1 WHERE testname<>'$testname'";
        }
   }
   
   //If the value of the $_POST[rank] is lower than DB
   elseif($class->ValueIsSmaller($testname, $rank)) { 
        $class->UpdateRank($testname, $rank); //Update Selected testname to selected rank
        for($tablerank = $rank; $tablerank <= $class->numberOfActiveTest(); $tablerank--) {
             //Update all tests - set rank to be 1 
             //higher on all tests that are now a lower value rank than selected one
             $query = "UPDATE tests SET rank=$tablerank+1 WHERE testname<>'$testname'";
        }
   }
}
Does anyone have a better idea (I'm sure someone does).

Posted: Tue Dec 19, 2006 4:48 pm
by Christopher
It seem like your UPDATE statements need to limit the WHERE to only a range of records and then you need to move the record that is changing.