Priority/order changes using arrays

[L4suicide]

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?

[L4suicide]

Just tell me if this is already the best way please At least then I can get on with coding ;-)

[zalucius]

Consider this:

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.

[zalucius]

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...
}

[L4suicide]

This still leaves me with problems. If I have many items, and some get done before they are supposed to, I will have gaps in the priorirty numbers.

A - 1, B - 2, C - 4, D - 5

To move B up or C down it is not just +/- 1

[zalucius]

This still leaves me with problems. If I have many items, and some get done before they are supposed to, I will have gaps in the priorirty numbers.

A - 1, B - 2, C - 4, D - 5

To move B up or C down it is not just +/- 1

Well.. when an item gets done, then you can execute an SQL query like this:
"UPDATE table SET priority = priority - 1 WHERE priority >= item_priority"

That will remove the gap.

[zalucius]

And if you call the Reorder() function, it will remove any gaps... so that's also an option.

[L4suicide]

Ah, and for adding a new item just +1 for all, makes sense, thanks

[L4suicide]

Ah sorry, missed something, I have many queues in one table, for different projects.

It's all good apart from getting the max value - is there something quicker than selecting by ORDER ASC LIMIT 1 in another query to get this value?

[zalucius]

you can get the max priority with the SQL COUNT function:

$items = mysql_fetch_assoc(mysql_query("SELECT COUNT(id) AS max_priority FROM table"));
// use $items['max_priority'] to get the number

or by the SQL MAX function

$items = mysql_fetch_assoc(mysql_query("SELECT MAX(priority) AS max_priority FROM table"));