How can I change the display order?

Question

I have a form and on this form the user can change the display order. This is reflected on a page that shows all our employees and we want to be able to control which order they appear.

enter image description here

When the form is submitted, I want to alter the other display orders in the database to reflect the change.

Some example data:

enter image description here

When they save that form, everything is updated to the db except the display order. That is handled in a separate function call.

function updateDisplayOrder($eid, $display_order_old, $display_order)
{
  if ($eid > 0)
  {
    if ($display_order != $display_order_old)
    {
      $result = db_query("SELECT * FROM {help_employees} WHERE active = 1 ORDER BY display_order");
      while ($arr = db_fetch_array($result))
      {
        if ($display_order > $display_order_old) 
        {
          if ($arr["display_order"] > $display_order_old && $arr["display_order"] <= $display_order)
          {
            db_query("UPDATE {help_employees} SET display_order = %d WHERE eid = %d", array($arr["display_order"] - 1, $arr["eid"]));
          }
        }
        else
        {
          if ($arr["display_order"] < $display_order_old && $arr["display_order"] >= $display_order)
          {
            db_query("UPDATE {help_employees} SET display_order = %d WHERE eid = %d", array($arr["display_order"] + 1, $arr["eid"]));
          }
        }
      }
      db_query("UPDATE {help_employees} SET display_order = %d WHERE eid = %d", $display_order, $eid);
    }
  }

}

What happens now:

3

What happens now? :) – André Laszlo Feb 23 '15 at 15:56

Javi Mollá · Answer 1 · 2015-02-23T16:27:18.970

If inserting a new employee:

db_query("UPDATE {help_employees} SET display_order = display_order + 1 WHERE display_order >= :display_order", $display_order);

Else if $display_order > $display_order_old

db_query("UPDATE {help_employees} SET display_order = display_order + 1 WHERE display_order >= :display_order AND id <> :id", $display_order, $id)`;

Else

db_query("UPDATE {help_employees} SET display_order = display_order + 1 WHERE display_order >= :display_order AND display_order < :display_order_old AND id <> :id", $display_order, $display_order_old, $id);

Where $display_order_old, $display_order and $id are values of the current modified employee

I think you wrong in all case except employee order 2 become order 1 — Benjamin Poignant, Feb 23 '15 at 16:17

score 0 · Answer 2 · answered Feb 23 '15 at 16:21

remove your item from its current position

 db_query("UPDATE {help_employees}
     SET display_order = display_order - 1
     WHERE eid != %d AND active = 1 AND  display_order > %d",
       $eid,  $display_order_old);

add some space for its new position

 db_query("UPDATE {help_employees}
     SET display_order = display_order + 1
     WHERE eid != %d AND active = 1 AND  display_order >= %d",
       $eid,  $display_order);

modify it:

 db_query("UPDATE {help_employees} SET display_order = %d WHERE eid = %d ",
       $display_order, $eid);

How can I change the display order?

2 Answers2