Situation I have a list of things that need doing in a database table. I want to be able to edit the order in which they get done.
Current solution I have a value in the database listing a ‘priority’ value where the higher the value the closer to the top of the list it is. This number is defaultly 0. I then modify numbers when moving to the top or bottom of the list so that either the target item has 0 or the top number.
Problem When wanting to move an item up or down this list it gets a little messy. I can’t just +1 in the database because some items will be completed (despite order of the list) before they should be leaving gaps in the numbering system. In my code to move an item to top or bottom I effectively re-number every item:
get all item ids and priority from database
put into an array, sort the array
if move to bottom then set manually that id to be lowest priority in db
loop array assigning next priority to item in the db
if move to top manually assign that id as last one
Now I wanted to do something similar to move up/down and just ‘swap’ the values, which I could do at the last step, or I could do it all in the array, but it seems real messy and my array skills are not good.
Is there a better method for something like this? If not then how would I swap to values in the array before looping to put back in db?
Have a function to “reorder” priorities, something like this:
// NOT TESTET, just to show concept.
function Reorder()
{
// query to get all id's, ordered by priority ASC.
$things = mysql_query("SELECT id FROM table ORDER BY priority ASC");
// iterate thru all items, and give each a new priority
$priority = 0;
while($row = mysql_fetch_assoc($things))
{
mysql_query("UPDATE table SET priority = ".$priority." WHERE id = ".$row['id']);
// add 1 to $priority, then the next item will have a higher priority of 1.
$priority++;
}
}
Then have a function to move to top and move to bottom.
// NOT TESTET, just to show concept
function MoveToTop($id)
{
// get the priority of the item, and the max priority available
$item = mysql_fetch_assoc(mysql_query("SELECT priority, MAX(priority) AS max_priority FROM table WHERE id = ".$id));
// move all items above the current item, one prioroty down.
mysql_query("UPDATE table SET priority = priority - 1 WHERE priority >= ". $item['priority']);
// set priority of the current item to the max priority
mysql_query("UPDATE table SET priority = ".$item['max_priority']." WHERE id = ".$id);
// Reorder() could be called here, to make absolutely sure everything is in order...
}
// NOT TESTET, just to show concept
function MoveToBottom($id)
{
// get the priority of the item.
$item = mysql_fetch_assoc(mysql_query("SELECT priority FROM table WHERE id = ".$id));
// move all items below the current item, one priority up (to make room)
mysql_query("UPDATE table SET priority = priority + 1 WHERE priority <= ". $item['priority']);
// position the current item at priority 0 (zero)
mysql_query("UPDATE table SET priority = 0 WHERE id = ".$id);
// Reorder() could be called here, to make absolutely sure everything is in order...
}
Perhaps you can figure out how adding or deleting items could work, based on the above concept.
To swap items, you could construct a moveup and movedown function
// NOT TESTET, just to show the contept
function MoveUp($id)
{
// if you are not sure everything is in order, use the Reorder() function here.
// get the priority of the item
$item = mysql_fetch_assoc(mysql_query("SELECT priority FROM table WHERE id = ".$id));
// move the item 1 above the current item, move it one down.
mysql_query("UPDATE table SET priority = priority - 1 WHERE priority = ".($item['priority']+1));
// move the current item one up.
mysql_query("UPDATE table SET priority = priority + 1 WHERE id = ".$id);
// Reorder() could be called here, to make absolutely sure everything is in order...
}
Move down is almost the same, just the other way.
An alternative, if you know the id’s of both items.
// NOT TESTET, just to show the concept
function Swap($from_id, $to_id)
{
// get the priority of the "from" item.
$from = mysql_query("SELECT priority FROM table WHERE id = ".$from_id);
// get the priority of the "to" item.
$to = mysql_query("SELECT priority FROM table WHERE id = ".$to_id);
// set priority of the "from" item, to the prioroty of the "to" item.
mysql_query("UPDATE table SET priority = ".$to." WHERE id = ".$from_id);
// set priority of the "to" item, to the priority of the "from" item.
mysql_query("UPDATE table SET priority = ".$from." WHERE id = ".$to_id);
// Reorder() could be called here, to make absolutely sure everything is in order...
}