Move position of MySQL row up/down using php

Question

I am fetching my page's menu from a MySQL table as an unordered list <ul></ul> ORDERERED BY their position, Here is my MySQL table's screenshot

enter image description here

I want my query to work in a way like when I fetch record of page_id_pk=3, it SET its page_position from 3 to 2 and updates it so now it holds page_position 2. The rest of the pages (rows) move down by an increment of one. I don't want to just swap two rows but to change their position i.e their page_id_pk sequence will remain same just their position gets changed.

Can you use something like set page_position=page_position+1 where page_position > the_position_you_set — Stig Husby, Nov 05 '13 at 07:49
You can use MYSQL's FIELD function in your order by clause [Custom Order By in MYSQL][1] [1]: http://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql — Zo Has, Nov 05 '13 at 07:50
@DamienJoe I am afraid you don't quite understand the question. — Your Common Sense, Nov 05 '13 at 07:53
@Serhat Akay I am fetching values ORDER BY page_position ASC so that matters the sequence of the menu — Faisal, Nov 05 '13 at 09:39

sectus · Accepted Answer · 2013-11-06T00:25:30.390

12

Something like this for swapping

UPDATE `position_table` SET `page_position` = CASE  
                        WHEN (`page_position`=1)  THEN 2   
                        WHEN (`page_position`=2)  THEN 1
                        END 
WHERE `page_position` in (1, 2) -- last line to prevent whole table scan

And this for moving

-- old - old_position, new - new_position

UPDATE `position_table` SET
    `page_position` = CASE
        WHEN (`page_position` = old) THEN 
            new                                -- replace new within old
        WHEN (`page_position` > old and `page_position` <= new) THEN 
            `page_position`- 1                 -- moving up
        WHEN (`page_position` < old and `page_position` >= new) THEN 
            `page_position`+ 1                 -- moving down
        ELSE 
            `page_position`                    -- otherwise lets keep same value.
      END

edited Nov 06 '13 at 00:25

answered Nov 05 '13 at 07:54

sectus

15,605
5
55
97

@Faisal, just added some comments. – sectus Nov 05 '13 at 09:21
And when new position is small - lets say position was 1 set it 3, then? – Faisal Nov 05 '13 at 09:34
Thank you so much you've been very supportive :) – Faisal Nov 06 '13 at 02:34

score 0 · Answer 2 · answered Nov 05 '13 at 07:50

0

What will happen if you change page_position 2 to 1 ? If you can live with page_position number swap then do it that way...So page_position 3 to 2 is selected then make current 2 to 3 This way you can move them around one by one. if 5 become 6 then old 6 becomes 5

answered Nov 05 '13 at 07:50

Serhat Akay

536
3
10

I need to move them positions instead of swapping because when I have 100 pages if I want to move 100 on 1st then the 1st one will become 100th which I not what I want – Faisal Nov 05 '13 at 09:01
That would be a rare case but still if you want to move 10th position to 5 then you will end up doing 5 swaps. 10<->9,9<->8,8<->7,7<->6,6<->5....If I were you thats what I would do. Moving 100th to 1st place might be a very rare. – Serhat Akay Nov 05 '13 at 11:17

Move position of MySQL row up/down using php

2 Answers2