SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Priority/order changes using arrays

    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?
    Last edited by L4suicide; Mar 25, 2011 at 20:25. Reason: Sucky reasoning.

  2. #2
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just tell me if this is already the best way please At least then I can get on with coding ;-)

  3. #3
    SitePoint Zealot zalucius's Avatar
    Join Date
    Jul 2007
    Location
    Denmark
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Consider this:

    Have a function to "reorder" priorities, something like this:
    Code PHP:
    // 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.
    Code PHP:
    // 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.
    Last edited by zalucius; Mar 26, 2011 at 07:19. Reason: A few typos
    zalucius

  4. #4
    SitePoint Zealot zalucius's Avatar
    Join Date
    Jul 2007
    Location
    Denmark
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To swap items, you could construct a moveup and movedown function
    Code PHP:
    // 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.
    Code PHP:
    // 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...
    }
    zalucius

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Zealot zalucius's Avatar
    Join Date
    Jul 2007
    Location
    Denmark
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by L4suicide View Post
    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

  7. #7
    SitePoint Zealot zalucius's Avatar
    Join Date
    Jul 2007
    Location
    Denmark
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And if you call the Reorder() function, it will remove any gaps... so that's also an option.
    zalucius

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, and for adding a new item just +1 for all, makes sense, thanks

  9. #9
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    SitePoint Zealot zalucius's Avatar
    Join Date
    Jul 2007
    Location
    Denmark
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can get the max priority with the SQL COUNT function:

    Code PHP:
    $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


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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •