CODEWITHSUNDEEP
phpmysqlarraysloops
cantsay
cantsay

Reputation: 2046

How to change the order of a list?

I'm using PHP/MySQL but just looking for pseudo-code really

How can I allow a user to change which order a list of items displays?

Say the user moves #4 to #1 do I change #4 to #0 then foreach row (1-3) increase it by 1 so it's 2-4 and then change #0 to #1?

Is this the best way?

Update: it's for a to-do list, and I won't just be swapping the order of items (i.e. swap #1 and #4 but will be moving items in front of others)

I have the items in a SQL db with item_id, task, completed, order_id

it's order_id that I need to change

Upvotes: 0

Views: 3263

Answers (3)

jgroenen
jgroenen

Reputation: 1326

I think you had it right already:

UPDATE items SET order_id = 0 WHERE order_id = 4;
UPDATE items SET order_id = order_id + 1 WHERE  order_id >= 1 AND order_id < 4;
UPDATE items SET order_id = 1 WHERE order_id = 0;

To move item with order_id i up to position j (i > j):

UPDATE items SET order_id = 0 WHERE order_id = i;
UPDATE items SET order_id = order_id + 1 WHERE  order_id >= j AND order_id < i;
UPDATE items SET order_id = j WHERE order_id = 0;

To move item with order_id i down to position j (j > i):

UPDATE items SET order_id = 0 WHERE order_id = i;
UPDATE items SET order_id = order_id - 1 WHERE  order_id > i AND order_id <= j;
UPDATE items SET order_id = j WHERE order_id = 0;

Another option would be to add a previous_sibling. Assuming you already have the list loaded, so you know that the previous siblings are [1 => null, 2 => 1, 3 => 2, 4 => 3, 5 => 4]:

UPDATE items SET previous_sibling = 3 WHERE id = 5; #previous_sibling of 4
UPDATE items SET previous_sibling = null WHERE id = 4; # previous_sibling of 1
UPDATE items SET previous_sibling = 4 WHERE id = 1;

Upvotes: 5

deceze
deceze

Reputation: 522625

Assuming your want to set the task with id $task_id to $order_id:

UPDATE `table`
   SET order_id = order_id + 1
 WHERE order_id > $order_id;

UPDATE `table`
   SET order_id = $order_id
 WHERE task_id  = $task_id;

And just to make sure the order ids are always continuous and without gaps:

  SELECT @numrow := 0;

  UPDATE `table`
     SET order_id = @numrow := @numrow + 1;
ORDER BY order_id ASC;

This re-numbers all order ids.

Upvotes: 0

starshine531
starshine531

Reputation: 591

I have found that it's usually best to do sorting with javascript (i.e. jquery), if feasible, because it prevents having to hit the backend every time the user wants to resort.

However, if you want to use php to do this, I'd recommend checking out array_push, array_pop and such. They can accomplish what you're trying to do. See http://www.php.net/manual/en/function.array-push.php#56479 for an example.

Upvotes: 0

Related Questions