MySQL Reorder Sequence Number

I am sure a lot peoples want to have their own sequence number order for their table.
For example menu list, not everyone would like to order by `name` or `id`.
What normally people do in website is give a text box file to fill in the sequence number which the most easiest way and direct way.
What you have to do just update the number that user key-in.

Today I would like to share for what if you have a new record and u want to put it in the middle?
To do in the normal way you have to retype and change the order/sequence number. But I would like to share with you all simple way.
MySQL tbl_MENU table:

ID NAME SEQ
1 Home 1
2 Blog 2
3 Download 3
4 Contact Us 4
5 About Us 5

Now I would like to add a new Menu which "Latest News" after the "Home".  First of course you need to insert a new data first.

INSERT tbl_menu(ID, NAME, SEQ) VALUES (NULL, 'Latest News', 6);

ID NAME SEQ
1 Home 1
2 Blog 2
3 Download 3
4 Contact Us 4
5 About Us 5
6 Latest News 6

Now I would like to change Seq No. 6 to Seq No. 2

After that we can run this query to update the sequence in this way:

UPDATE tbl_menu SET seq = seq + 1 WHERE seq >= 2 AND seq <= 6;
UPDATE tbl_menu SET seq = 2 WHERE id = 6;

The first statement is run the current sequence number to add one value, that means the table would like this:

ID NAME SEQ
1 Home 1
2 Blog 3
3 Download 4
4 Contact Us 5
5 About Us 6
6 Latest News 7

The next query is update the ID:6 sequence number to 2

ID NAME SEQ
1 Home 1
2 Blog 3
3 Download 4
4 Contact Us 5
5 About Us 6
6 Latest News 2

Finally you can run your display query like this

SELECT * FROM `tbl_menu ORDER BY seq;

ID NAME SEQ
1 Home 1
6 Latest News 2
2 Blog 3
3 Download 4
4 Contact Us 5
5 About Us 6

Leave a Reply

Your email address will not be published. Required fields are marked *