Item Sorting Mechanism

[blackcell]

Hello, I need a second person to look over some code for me. I have stared at it for some time now and usually can work bugs out but this time I just can't seem to spot it.

Basically I have a simple ticket system I have made and on one menu there is a pane to view your assigned tickets. In there are four buttons beside each ticket for four actions:

 

-Move to Top

-Move Up

-Move Down

-Move to Bottom

 

For the most part this works. I can move up and down and top and bottom but sometimes I have to click multiple times like there may be gaps resulting the need to move something multiple times in one direction to be below/above the adjacent item. The biggest thing that boggles my mind is that sometimes I have to click the Move to top or move to bottom multiple times and it should work the first time since it basically sets the priority to 999 for bottom and -999 for top.

 

This is my first attempt at anything like this so any help or advise on alternate methods would be greatly appreciated.

 

Here is the code:

<?php
//*****************************This is to renumber the priority and take out gaps.**************************************
    $x = 1;
    $sqlQuery = "SELECT * FROM `tickets_log` WHERE `log_assignedID` = '".$_SESSION['SUPPORTID']."' AND `log_status` != 'Closed' ORDER BY `log_assignedPriority`";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
    while($row = mysql_fetch_array($sqlResult)){
        $log_number = $row["log_number"];
        $log_status = $row["log_status"];
        $sqlQuerySerialize = "UPDATE `tickets_log` SET `log_assignedPriority` = '$x' WHERE `log_number` = '$log_number' LIMIT 1";
        $sqlResultSerialize = mysql_query( $sqlQuerySerialize ) or
            die("Could not execute query:<br>" . mysql_error());
        $x++;
    }
//*****************************End or Gap removing query*************************************************


if($motion == "up" && $assignedPriority != ""){

    //Original Log Work to move UP one number
    $Original_Priority = $assignedPriority - 1;
    $sqlQuery = "SELECT `log_number` FROM `tickets_log` WHERE `log_assignedPriority` = '$Original_Priority'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
    $rowCheck = mysql_fetch_assoc($sqlResult);
        $Original_Log = $rowCheck["log_number"];

    //New Log Work to move DOWN number
    $sqlQuery = "SELECT `log_number` FROM `tickets_log` WHERE `log_assignedPriority` = '$assignedPriority'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
    $rowCheck = mysql_fetch_assoc($sqlResult);
        $Moving_Log = $rowCheck["log_number"];


    //Write Back
    //New update to assignedPriority Number  minus 1
    $newPriority = $assignedPriority - 1;
    $sqlQuery = "UPDATE `tickets_log` SET `log_assignedPriority` = '$newPriority' WHERE `log_number` = '$Moving_Log'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());

    //Original update to assignedPriority Number
    $sqlQuery = "UPDATE `tickets_log` SET `log_assignedPriority` = '$assignedPriority' WHERE `log_number` = '$Original_Log'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
}

if($motion == "down" && $assignedPriority != ""){

    //Original Log Work to move UP one number
    $Original_Priority = $assignedPriority + 1;
    $sqlQuery = "SELECT `log_number` FROM `tickets_log` WHERE `log_assignedPriority` = '$Original_Priority'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
    $rowCheck = mysql_fetch_assoc($sqlResult);
        $Original_Log = $rowCheck["log_number"];

    //New Log Work to move DOWN number
    $sqlQuery = "SELECT `log_number` FROM `tickets_log` WHERE `log_assignedPriority` = '$assignedPriority'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
    $rowCheck = mysql_fetch_assoc($sqlResult);
        $Moving_Log = $rowCheck["log_number"];


    //Write Back
    //New update to assignedPriority Number  minus 1
    $newPriority = $assignedPriority + 1;
    $sqlQuery = "UPDATE `tickets_log` SET `log_assignedPriority` = '$newPriority' WHERE `log_number` = '$Moving_Log'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());

    //Original update to assignedPriority Number
    $sqlQuery = "UPDATE `tickets_log` SET `log_assignedPriority` = '$assignedPriority' WHERE `log_number` = '$Original_Log'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
}

if($motion == "bottom" && $assignedPriority != ""){

    //New Log Work to move DOWN number
    $sqlQuery = "SELECT `log_number` FROM `tickets_log` WHERE `log_assignedPriority` = '$assignedPriority'";
    ECHO $sqlQueryMove;
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
    $rowCheck = mysql_fetch_assoc($sqlResult);
        $Moving_Log = $rowCheck["log_number"];

    //New update to assignedPriority Number  minus 1
    $sqlQuery = "UPDATE `tickets_log` SET `log_assignedPriority` = '999' WHERE `log_number` = '$Moving_Log'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
}

if($motion == "top" && $assignedPriority != ""){

    //New Log Work to move DOWN number
    $sqlQuery = "SELECT `log_number` FROM `tickets_log` WHERE `log_assignedPriority` = '$assignedPriority'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
    $rowCheck = mysql_fetch_assoc($sqlResult);
        $Moving_Log = $rowCheck["log_number"];

    //New update to assignedPriority Number  minus 1
    $sqlQuery = "UPDATE `tickets_log` SET `log_assignedPriority` = '-999' WHERE `log_number` = '$Moving_Log'";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
}
//*****************************This is to renumber the priority and take out gaps.**************************************
    $x = 1;
    $sqlQuery = "SELECT * FROM `tickets_log` WHERE `log_assignedID` = '".$_SESSION['SUPPORTID']."' AND `log_status` != 'Closed' ORDER BY `log_assignedPriority`";
    $sqlResult = mysql_query( $sqlQuery ) or
        die("Could not execute query:<br>" . mysql_error());
    while($row = mysql_fetch_array($sqlResult)){
        $log_number = $row["log_number"];
        $log_status = $row["log_status"];
        $sqlQuerySerialize = "UPDATE `tickets_log` SET `log_assignedPriority` = '$x' WHERE `log_number` = '$log_number' LIMIT 1";
        $sqlResultSerialize = mysql_query( $sqlQuerySerialize ) or
            die("Could not execute query:<br>" . mysql_error());
        $x++;
    }
//*****************************End or Gap removing query*************************************************
?>

[fenway]

This really isn't a mysql issue... it's about how to manipulate sort orders; the fact that you're storing it a DB is another issue entirely (i.e. there's no logic on the mysql side, it's just an update).

 

Easiest way is to ignore existing numbers, and update ALL of them agian.

[blackcell]

I am ignoring numbers all together and updating...

[fenway]

You talk about "gaps"... you can't have gaps until you have numbers, which means you're assigning them too soon.

[blackcell]

If you looked at the code you would see that I pull all of the pertinent records disregarding specific numbers and order them by the number they currently have. At this point the records may contain gaps like this:

Record 1 = 1

Record 2 = 3

Record 3 = 4

Record 4 = 5

Record 5 = 10

 

The whole purpose of this part is to renumber them to eliminate gaps so:

Record 1 = 1

Record 2 = 3

Record 3 = 2

Record 4 = 4

Record 5 = 5

 

I then go into the phase of executing what the user wants (move up, down, top or bottom).

 

Once I execute what the user wants I run the elimination of gaps routine to tighten things back up.

 

[fenway]

I know how to read code.  And yes... that's precisely why it's not working -- don't renumber until you SAVE, the gaps don't matter.  I'm moving this to the php foru

[blackcell]

That worked thanks fenway.